Mysqldump full-scale backup and recovery

MySQL User management

Create an authorized user and password? Grant all on the User1 ' identified by ' [email protected] ';
The user who creates the connection management database in MySQL creates a user who can assign the appropriate permissions for the user to access the specified library
Create a user and authorize a user to access a library, have permission to access all the libraries locally, and view the permissions records saved in that user's re-database

mysql> grant all on *.* to ‘user1‘ identified by ‘[email protected]‘;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> show grants for user1;+--------------------------------------------+| Grants for [email protected]% ? ? ? ? ? ? ? ? ? ? ? ? |+--------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO ‘user1‘@‘%‘ |+--------------------------------------------+1 row in set (0.00 sec)

Create a user and specify access rights and hosts with a password? Grant Select,update,insert to ' user1 ' @ ' 192.168.1.234 ' identified by ' [email protected] ';
Assign specific permissions to a user when they are created, allowing only a subset of SQL operations to control their access
Use show grants for to view a corresponding authorization data, such as the user authorized access to the MySQL user host, permissions, access to the library information, mysql5.7 version will no longer display the encrypted password of the authorized user *

mysql> grant SELECT,UPDATE,INSERT on nice.* to ‘user1‘@‘192.168.1.234‘ identified by ‘[email protected]‘;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> show grants for [email protected]‘192.168.1.234‘;+---------------------------------------------------------------------+| Grants for [email protected] ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?  |+---------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘user1‘@‘192.168.1.234‘ ? ? ? ? ? ? ? ? ? ? ? || GRANT SELECT, INSERT, UPDATE ON `nice`.* TO ‘user1‘@‘192.168.1.234‘ |+---------------------------------------------------------------------+2 rows in set (0.00 sec)

Query the created data for authorized users? Show grants for [email protected] ' 192.168.1.234 ';
After the mysql5.7 version, show grants query the user authorization statement no longer shows the authorized user encrypted password this information, in Show grants query results, you can copy its results in a way to re-create another user or authorization, but because the encryption password is no longer displayed, It is also recommended that you only use the permissions previously created by the user and specify the authorization library, using the initial method of grant to create
Use show grants for to query only authorization statements when a user is authorized, and the statement does not contain encrypted passwords that require authentication when a user logs on
The logged data for the authorized user of the query is as follows:

mysql> show grants for [email protected]‘192.168.1.234‘;+---------------------------------------------------------------------+| Grants for [email protected] ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?  |+---------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘user2‘@‘192.168.1.234‘ ? ? ? ? ? ? ? ? ? ? ? || GRANT SELECT, INSERT, UPDATE ON `nice`.* TO ‘user2‘@‘192.168.1.234‘ |+---------------------------------------------------------------------+2 rows in set (0.00 sec)

Common SQL statements

Number of data rows in the statistics table *select COUNT () from mysql.user;*
The number of rows in the statistics table is not recommended when there is too much data, because statistics can take a long time to query and consume server resources *

mysql> select count(*) from mysql.user;+----------+| count(*) |+----------+| 6 ? ? ?  |+----------+1 row in set (0.00 sec)

Queries all contents stored in the specified data table Select from Mysql.db\g;
Querying all the contents of the database, such as viewing the authorized data here, does not recommend such a query operation, because if the data is more, it will cause the query time is too long, expensive server resources, \g in a more readable way to display
The query results show the authorization of the User2 user to access the Nice library through a 1.234 host

mysql> select * from mysql.db\G;*************************** 3. row *************************** ? ? ? ?  Host: 192.168.1.234 ? ? ? ?  Db: nice ? ? ? ?  User: user2 ? ? ? ?  Select_priv: Y ? ? ? ?  Insert_priv: Y ? ? ? ?  Update_priv: Y ? ? ? ?  Delete_priv: N

Querying a few fields in a database select Db,user from mysql.db;
Queries one or two fields in a table, from the specified query's libraries and tables, and the fields of the specified query after select. All data under the query field is listed

mysql> select db,user from mysql.db;+--------------------+---------------+| db ? ? ? ? ? ? ? ? | user ? ? ? ?  |+--------------------+---------------+| nice ? ? ? ? ? ? ? | user1 ? ? ? ? || nice ? ? ? ? ? ? ? | user2 ? ? ? ? || performance_schema | mysql.session || sys ? ? ? ? ? ? ?  | mysql.sys ? ? |+--------------------+---------------+4 rows in set (0.00 sec)

Fuzzy matching query?? Select from mysql.db where host like ' 192.168.% ' \g;
Fuzzy match query Some fields, such as the information of the authorized user to query MySQL, the query results include the authorized user name and the host IP address that is allowed to access, \g display in an easy-to-read way

mysql> select * from mysql.db where host like ‘192.168.%‘\G;*************************** 1. row *************************** ? ? ? ?  Host: 192.168.1.234 ? ? ? ?  Db: nice ? ? ? ?  User: user1 ? ? ? ?  Select_priv: Y ? ? ? ?  Insert_priv: Y

Insert a piece of data into the datasheet? INSERT into HOLLE.TB1 values (, ' 2233 ');
Query the field structure in the data table and insert a piece of data into the data table, the data table for this presentation is only numeric and character data types

mysql> use holle;Database changedmysql> show tables;+-----------------+| Tables_in_holle |+-----------------+| tb1 ? ? ? ? ? ? |+-----------------+1 row in set (0.00 sec)mysql> desc holle.tb1;+-------+----------+------+-----+---------+-------+| Field | Type ? ? | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| id ?  | int(4) ? | YES  | ? ? | NULL ?  | ? ? ? || name  | char(40) | YES  | ? ? | NULL ?  | ? ? ? |+-------+----------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> insert into holle.tb1 values (23, ‘2233‘);Query OK, 1 row affected (0.01 sec)mysql> select * from  holle.tb1;+------+------+| id ? | name |+------+------+| ? 23 | 2233 |+------+------+1 row in set (0.00 sec)

Update (change) all data? Update holle.tb1 set id=33 where name= ' ";
This is to update all the data in the table, you need to be cautious, will update the table data to the same value, mysql5.7 invalid. Executing an action statement on mysql5.7, the contents of the table are not updated/changed

mysql> update holle.tb1 set id=33 where name=‘23‘;Query OK, 0 rows affected (0.00 sec)Rows matched: 0 Changed: 0 Warnings: 0mysql> select * from holle.tb1;+------+------+| id ? | name |+------+------+| 23 ? | 2233 || 66 ? | 233  |+------+------+2 rows in set (0.00 sec)

Empty all contents of the specified table ? truncate TABLE holle.tb1;
The operation clears all data from the specified table, emptying the TB1 table in the Holle library.

mysql> select * from holle.tb1;+------+------+| id ? | name |+------+------+| 23 ? | 2233 || 66 ? | 233  |+------+------+2 rows in set (0.00 sec)mysql> truncate table holle.tb1;Query OK, 0 rows affected (0.03 sec)mysql> select * from holle.tb1;Empty set (0.00 sec)

Delete data table? drop table holle.tb1;
Deletes the specified data table, and the data and structure in the table are deleted together * *

mysql> show tables;+-----------------+| Tables_in_holle |+-----------------+| tb1 ? ? ? ? ? ? |+-----------------+1 row in set (0.00 sec)mysql> drop table holle.tb1;Query OK, 0 rows affected (0.02 sec)mysql> show tables;Empty set (0.00 sec)

Delete a database??drop database holle;
After you delete the Holle library, the number of databases changes from the previous 6 libraries to 5 *

mysql> show databases;+--------------------+| Database ? ? ? ? ? |+--------------------+| information_schema || holle ? ? ? ? ? ?  || mysql ? ? ? ? ? ?  || nice ? ? ? ? ? ? ? || performance_schema || sys ? ? ? ? ? ? ?  |+--------------------+6 rows in set (0.00 sec)mysql> drop database holle;Query OK, 0 rows affected (0.00 sec)mysql> show databases;+--------------------+| Database ? ? ? ? ? |+--------------------+| information_schema || mysql ? ? ? ? ? ?  || nice ? ? ? ? ? ? ? || performance_schema || sys ? ? ? ? ? ? ?  |+--------------------+5 rows in set (0.00 sec)

# MySQL Database backup recovery

back up MySQL library * *mysqldump-uroot [email protected] MySQL >/tmp/mysql.sql**
Mysqldump is backed up in a full-scale manner, which operates on a Linux system and is only suitable for small database backups, which can be time-consuming if the database is large.

[[email protected] ~]# mysqldump -uroot [email protected] mysql >/tmp/mysql.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[[email protected] ~]# ll -h /tmp/mysql.sql -rw-r--r-- 1 root root 1.1M 8月 17 13:11 /tmp/mysql.sql

Recover MySQL Library
First, you need to create an empty library in the database, where to restore the database storage, the recovery library using the MySQL command, after creating the library log out of the database login, using the MySQL command to redirect the backup file to the newly created empty library, and then view the data table in the library

mysql> create database mysql2;Query OK, 1 row affected (0.00 sec)mysql> exitBye[[email protected] ~]# mysql -uroot [email protected] mysql2 < /tmp/mysql.sql mysql> use mysql2Database changedmysql> show tables;+---------------------------+| Tables_in_mysql2 ? ? ? ?  |+---------------------------+| columns_priv ? ? ? ? ? ?  || db ? ? ? ? ? ? ? ? ? ? ?  || engine_cost ? ? ? ? ? ? ? |

MySQL Backup table
The Backup data table is backed up to the file along with the data in the table

[[email protected] ~]# mysqldump -uroot [email protected] mysql2 user > /tmp/user.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[[email protected] ~]# cat /tmp/user.sql -- MySQL dump 10.13 Distrib 5.7.22, for Linux (x86_64)---- Host: localhost Database: mysql-- -------------------------------------------------------- Server version   5.7.22/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;

Recovering the user table

mysql> use mysql2;mysql> drop table user;Query OK, 0 rows affected (0.00 sec)mysql> exitBye[[email protected] ~]# mysql -uroot [email protected] mysql2 < /tmp/user.sqlmysql: [Warning] Using a password on the command line interface can be insecure.[[email protected] ~]# mysql -uroot [email protected] mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 31Server version: 5.7.22 Source distributionmysql> use mysql2Database changedmysql> show tables;+---------------------------+| Tables_in_mysql2 ? ? ? ?  |+---------------------------+| columns_priv ? ? ? ? ? ?  || db ? ? ? ? ? ? ? ? ? ? ?  |+----------省略部分显示------+| time_zone_transition_type || user ? ? ? ? ? ? ? ? ? ?  |+---------------------------+30 rows in set (0.01 sec)

Back up all the databases

[[email protected] ~]# mysqldump -uroot -p -A > /tmp/all.sqlEnter password: [[email protected] ~]# ll -h /tmp/all.sql -rw-r--r-- 1 root root 1.9M 8月 17 17:02 /tmp/all.sql

Back Up database table structure only
Backup does not store data in the table, only the creation of the table structure and statements back up, here to see some of the table, COLUMNS_PRIV the structure of the table

[[email protected] ~]# mysqldump-uroot [email protected]-D mysql2 >/tmp/user2.sqlmysqldump: [Warning] Using a password on the command line interface can is insecure. [[email protected] ~]# cat/tmp/user2.sql |less--MySQL dump 10.13 distrib 5.7.22, for Linux (x86_64)----host:local Host DATABASE:MYSQL2----------------------------------------------------------Server version 5.7.22/*!40101 SET @[ Email protected] @CHARACTER_SET_CLIENT */;/*!40101 SET @[email protected] @CHARACTER_SET_RESULTS */;/*! 40101 set @[email protected] @COLLATION_CONNECTION */;/*!40101 set NAMES UTF8 */;/*!40103 set @[email  Protected] @TIME_ZONE */;/*!40103 set time_zone= ' +00:00 ' */;/*!40014 set @[email protected] @UNIQUE_CHECKS, UNIQUE _checks=0 */;/*!40014 Set @[email protected] @FOREIGN_KEY_CHECKS, foreign_key_checks=0 */;/*!40101 set @[email  protected] @SQL_MODE, sql_mode= ' No_auto_value_on_zero ' */;/*!40111 SET @[email protected] @SQL_NOTES, SQL_ Notes=0 */;----table structure for table ' Columns_priv '--drop table IF EXISTS ' Columns_priv ';/*!40101 SET @saved_cs_client = @ @charac Ter_set_client */;/*!40101 Set character_set_client = UTF8 */; CREATE TABLE ' Columns_priv ' (? ' Host ' char COLLATE utf8_bin not NULL DEFAULT ' ',? ' Db ' char (+) COLLATE Utf8_bin not N ULL default ',? ' User ' char (+) COLLATE utf8_bin not null DEFAULT ' ',? ' table_name ' char (+) COLLATE utf8_bin NOT NULL DE FAULT ',? ' column_name ' char (+) COLLATE utf8_bin NOT null default ' ',? ' Timestamp ' Timestamp not null default Current_ti Mestamp on UPDATE current_timestamp,? ' Column_priv ' Set (' Select ', ' Insert ', ' UPDATE ', ' References ') CHARACTER set UTF8 not NULL default ', PRIMARY KEY (' Host ', ' Db ', ' User ', ' table_name ', ' column_name ')) Engine=myisam DEFAULT Charset=utf8 Collate=utf8_bin comment= ' Column privileges ';/*!40101 SET character_set_client = @saved_cs_client */;

Mysqldump full-scale backup and recovery