MYSQL BASIC COMMANDS

 BASIC COMMANDS

By default, there is no password for the root user. So, the first thing to do is to add a new one.
Change a user password:
 

#mysqladmin -u root password new_root_password

Create a new database
 

#mysqladmin -u root -p create database_name

Insert a sql file.
 

#mysql -u root -p database_name < file.sql

Access a database and give all the rights to a user on this database:
 

#mysql -u root -p database_name
>grant all on database_name.* to newuser@localhost identified by 'userpassword'; 
>flush privileges;
>exit

Get the list of tables in a database and display the entire contents of a table:
 

#mysql -u root -p
>use database_name;
>show tables;
>select * from table_name;

List your MySQL users:
 

#mysql -u root -p
>use mysql;
>select * from user;

Reboot the MySQL software:
 

#/etc/init.d/mysql restart

Reload the MySQL config:
 

#/etc/init.d/mysql reload

 ROOT USER PASSWORD RECOVERY

Stop MySQL
 

#/etc/init.d/mysql stop

Start MySQL server without password:
 

#mysqld_safe --skip-grant-tables &

Login to MySQL as root:
 

#mysql -u root
>use mysql;
>update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
>flush privileges;
>quit

Restart MySQL Server:
 

#/etc/init.d/mysql restart

Test your new root user password:
 

# mysql -u root -p

 MYSQL BACKUP

Backup all your databases:
-u = user ; -p = password (there is no space between the "-p" keyword and the password);
 

#mysqldump -u root -prootpassword --all-databases > mysql_databases_backup.sql

Backup a specific database:
 

#mysqldump -u root -prootpasssword --databases database_name > mysql_database_backup.sql

Check details about the mysqldump command.

Restore your MySQL backup:
 

#mysql -u root -proot < sql_backup.sql

It's important to stress that when you backup a database, this will NOT backup any MySQL user accounts.
As the MySQL user accounts are stored in the "user" table of the "mysql" database, it's a good idea to backup the "mysql" database too.
 

See below an example where the MySQL databases are saved periodically.

First we create a mysql user with restricted permissions to backup the databases:
 

#mysql -u root -p
>GRANT SELECT , SHOW DATABASES , LOCK TABLES ON * . * TO backupuser@localhost IDENTIFIED BY 'password' ;

Second we create a shell script by copying the line below in a file called mysqlbackup.sh in the /home/sam directory where sam is a user of your choice.
 

#vi /home/sam/mysqlbackup.sh
date=`date -I`; mysqldump --all-databases -u backupuser --password="password" >databasebackup-$date.sql

The date will be added at the end of the file name. By instance, a file saved on January 4, 2007 will be named databasebackup-2007-01-04.sql. 
 

#chown sam /home/sam/mysqlbackup.sh
#chmod 700 /home/sam/mysqlbackup.sh

Set appropriate rights for the mysqlbackup file. The file ownership is given to sam with full permission (read, write, execute)

Third we add the line below in our Linux user crontab file:
 

#crontab -e -u sam
30 00 * * * /home/sam/mysqlbackup.sh

The mysqlbackup shell script will be launched every day at 00:30 and thus will backup the MySQL databases.