OpenManiak.com - Useful MySQL commands

Other MySql
Last Change : Aug 21 2009 english flag

⚠️⚠️⚠️
Please check our website about
attractions in Western Switzerland !! (Please use english translation).

⚠️⚠️⚠️
Merci de consulter notre site sur les
activités à faire en Suisse romande !!

MySQL is a very powerful open source database server developed by the MYSQL AB company. It is one of the most used in the database server market.
To get more info about MySQL, have a look at the Wikipedia website.

logo mysql We will try here to give you some keys to use the MySQL database or backup it.
For complete details about how to use MySQL, see the MySQL Website.

MySQL can use several types of table engines such as ISAM, MyISAM, HEAP, InnoDB or Berkeley.
By default, a new table will use MyISAM, which will create 3 types of files located in /var/lib/mysql/database_name/:

table_name.mrd
table_name..myd
table_name..myi - structure
- data
- index

1. GRAPHICAL INTERFACE

2. BASIC COMMANDS

3. ROOT USER PASSWORD RECOVERY

4. MYSQL BACKUPS

GRAPHICAL INTERFACE

Phpmyadmin is the most famous interface to manage MySQL. It is written in PHP and requires the apache web server.
Phpmyadmin will be very useful if you are not an expert to handle SQL databases, tables or the server itself. We strongly recommend to install this tool:

#apt-get install phpmyadmin

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.