Debian, Ubuntu, LAMP. Very fast way. Part 2. Work with Mysql, mysql-backup.

This article is the article cycle LAMP installation on Debian/Ubuntu is a very fast way “
In part 1 of the manual on the lamp we set in mysql. And phpmyadmin. It would seem that still need to be happy. But knowing how to stick my ideology “fast way” when working with mysql server as an administrator.
Of course, for the work with mysql there is a bunch of adorable utilities that work in conjunction with php. But, first, there are not on all servers, and secondlywe are too lazy to open something, besides the ssh session, isn’t it)?
As an administrator, we need to do with mysql not much action. Načnëms.
First, enter the mysql console:

inky@Debian:~$ mysql -u root -p
Enter password:

Find out what we have in the console mysql we can by changing the salutation:


Begin. Create database:

mysql> create database dbname;

Give the right user database dbname muser. If this user does not exist, it is created automatically:

mysql> grant all on dbname.* to 'muser'@'localhost';

I would recommend not giving one user the right to multiple databases. but your right, of course.
Now we need to assign a password to the user muser:

mysql> set password for 'muser'@'localhost' = password('megapasswordhere');

Myself, megapasswordhere, change to your password.
It would seem, when what’s the fast way? Now explain.
Enter the following command:

mysql> create database dbname2; grant all on dbname2.* to 'muser2'@'localhost'; set password for 'muser2'@'localhost' = password('megapassword2here');

Only that we have created a database of dbname2, gave her all rights to the user muser2 and megapassword2here password to this user.
Now press the up arrow, correct digits 2 to 3, press enter … you. further, I think you misunderstand =) history is saved and after exiting the mysql-client. By the way, get out of it, you can press ctrl + D.
For the inattentive I want to notice that the database name is enclosed in inverted apostrophe (the letter f on your keyboard).
Now, we would do well to learn how to create dumps (backups) mysql databases and restore them. Immediately I say that I do not have any problems with the speed of the connection, or the server hdd space, so I will not archive or compress database.
Begin. First, we create a backup of an individual database, restore it to any other database (for example, to move to another hosting, where another ideology of the names database). These commands should be entered in the console of the server itself.

inky@Debian:~$ mysqldump --complete-insert --no-create-db -u muser -p dbname > dbname.sql
inky@Debian:~$ mysqldump --complete-insert --no-create-db -u muser -pmegapasswordhere dbname > dbname.sql

In the first case, you will be prompted for the password interactively, the second of … Well, is not secure, but convenient)
At the output we get all dump dump.
First, at the time the backup database will be blocked from any changes (new user who wrote a post in the Forum database backup does not poportit you dump). (optionaladd-locks)
Secondly, requests are recorded in full, with the name of the column. Increases the size, but reduces the possibility of errors (complete-insert)
Thirdly, the dump is not added to the CREATE DATABASE command, so we can restore the dump into any other base if desired. (no-createdbno-create-info)
Well, yes the dump will be in fajlike dbname.sql (file name is specified after you redirect output (>) you can use the design view >/backup/dbname.sql)
Well, let’s restore this backup:

inky@Debian:~$ mysql -u muser2 -pmegapassword2here dbname2 < dbname.sql

or with manual password:

inky@Debian:~$ mysql -u muser2 -p dbname2 < dbname.sql

Please note that we restore the backup in dbname2.
I don’t see any reason to create multiple database dumps. Ultimately, the value of such a dump is close to zero. If there is a strong desire-translation of the official manual for mysqldump is
And finally yummy:

inky@Debian:~$ mysqldump --add-locks --complete-insert --no-create-db --no-create-info -u muser2 -p dbname2 | mysql --host=remote-host -u muser2 -p dbname2

This command will transfer the database dbname dbname2 database on a remote host, the remote-host. Do not forget that you muser2 in this case, you want to allow remote access to the database: dbname2

mysql> grant all on dbname2.* to 'muser2'@'%';

and set the password for'muser2'@'%'
Oh and Ps
This is 90% of the tasks that I, as adminnedorostok, performed with mysql. As written aboveit takes 10% of the time from the whole work with mysql. Good luck to you.