Basic MySQL operations I – ServerAdminBlog

In this post, I will present few basic commands for MySQL administration. You’ll see how to create a database, create user, assign a database for specific user, etc. First I suppose that you have root password set and that you know it… (in next posts I will present few steps to set this password or to recover it…)

Create database and user

[root@hydra mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 248
Server version: 5.0.45 Source distribution
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql&gt;

Here you can see server version (in this case 5.0.45). With next command you can see all databases

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| birds              |
| mysql              |
| private             |
| bdd1             |
| test               |
+--------------------+
6 rows in set (0.05 sec)
 
mysql&gt;

If you want to create a new database, enter next command (remember that all SQL commands must be terminated with ; )

mysql> CREATE DATABASE db1;
Query OK, 1 ROW affected (0.00 sec)

Now we need to assign a user to newly created database. Also, we need to grant him all privileges for this database but restrict access and operations on another databases (except test database)

mysql> CREATE USER 'db1'@'localhost' IDENTIFIED BY 'db1pass';
Query OK, 0 ROWS affected (0.02 sec)
 
mysql> GRANT ALL PRIVILEGES ON db1.* TO 'db1'@'localhost' IDENTIFIED BY 'db1pass';
Query OK, 0 ROWS affected (0.01 sec)
 
mysql> quit;
Bye

To add readonly account you can set next privileges

GRANT SELECT ON db1.* TO 'db1'@'%';

If you replace “localhost” with “%”, the user will be able to connect from remote host.

Backup database

Mysqldump is a command-line utility that is used to generate the logical backup of the MySQL database. It produces the SQL file with statements that can be used to recreate the database objects and data. When we restore the database, the command executes all the SQL statements in the backup file to create tables and insert the data. If you have a large database, then the restoration process can takes a quite long time to complete so it isn’t easy to restore a large database

Basic syntax

# mysqldump -u [user-name] –p [password] [options] [database-name] [tablename] &gt; [dumpfilename.sql]

Examples:

Backup single database

# mysqldump -u root -p mydb > mydb.sql

Backup several databases at once

# mysqldump -u root -p --databases db1 db2 > db1db2.sql

Backup all databases at once

# mysqldump -u root -p --all-databases > alldbs.sql

Backup only mydb structure (tables without data)

# mysqldump -u root -p --no-data mydb > mydb.sql

Backup only specific tables (in this case: table1, table2, table3

# mysqldump -u root -p mydb table1 table2 table3 > mydb.sql

Backup only database data (no create statements)

# mysqldump -u root -p mydb --no-create-info > mydb.sql

Backup all databases with events and routines

# mysqldump --routines --events --flush-privileges --all-databases > alldbs.sql

Restore database

To restore the database from sql backup, the first thing you need to do is to create the database with

mysql> CREATE DATABASE mydb;

Then you can restore the db with

# mysql -u root -p mydb < mydb.sql

Leave a Reply