Thu, 10 Aug 2006

mysqldump

// To backup a database

mysqldump -u ironmossman -p ironmoss_db > backup.sql


// To backup a database via SSH

ssh ironmossman@teammossman.com "mysqldump -u ironmossman -psecret ironmoss" > backup.sql

Posted at: 21:38 | category: /database/mysql | Comments ()

MySQL - set initial root password

Remember to set a password for the MySQL root user!
This is done with:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h coyner.net password 'new-password'

Posted at: 21:38 | category: /database/mysql | Comments ()

MySql Cheat Sheet 

These are the main MySQL clients and processes (mysqld):

mysqld - MySQL server daemon
safe_mysqld - Server process monitor
mysqlaccess - Tool for creating MySQL users
mysqladmin - Utility for administering MySQL
mysqldump - Tool for dumping the contents of a MySQL database
mysql - Command line interface to MySQL
mysqlshow - List all MySQL database


These are the main Field Types in SQL:

INTEGER - A whole number
VARCHAR(10) - Up to 10 characters.
CHAR(10) - Fixed number of characters
DATE - A date
DATETIME - Date and time
FLOAT - Floating point numbers



These are some Field Types specific to MySQL:

TEXT - Allows up to 65535 characters
DECIMAL(10,2) - Up to 10 digits before the point, 2 after.


Here we Create a database:

$ mysqladmin --user=Theoden --password=xxx create database addressdb


Using that database:

$ mysql --user=Theoden --password=xxx
mysql> USE addressdb


Create a table in that database:

mysql> CREATE TABLE p_addr (i INTEGER PRIMARY KEY,address TEXT,email VARCHAR(30),pincode
DECIMAL(10),phone DECIMAL(15),website TEXT);


Add a column called "name" to the table:

mysql> ALTER TABLE p_addr ADD name VARCHAR(30);


Inserting values into table:

mysql> INSERT INTO p_addr VALUES (1,"My, present,
address","Theoden@localhost",681024,2122536,
"http://your.website.com","Theoden");


List the contents of the table:

mysql> SELECT * FROM p_addr;


Delete a row from the table:

mysql> DELETE FROM p_addr WHERE i=1;


Rename a column in the table from "address" to "home_address":

mysql> ALTER TABLE p_addr CHANGE address home_address INTEGER;
Note: You cannot use this method to rename a column which is a primary key.


Change an existing record in the table:

mysql> UPDATE p_addr SET name="William" WHERE i=2;


Delete the table from the database:

mysql> DROP TABLE p_addr;


List the databases available in 'your' MySQL installation:

$ mysqlshow --user=Theoden --password=xxx

This will return the following:

+-----------+
| Databases |
+-----------+
| addressdb |
| myblog |
| mysql |
| test |
+-----------+


List the tables in the database "addressdb":

$ mysqlshow --user=Theoden --password=xxx addressdb
Database: addressdb

This will return the following:

+---------+
| Tables |
+---------+
| p_addr |
| mytble |
| phonebk |
+---------+

Posted at: 21:38 | category: /database/mysql | Comments ()