【整 1.2.3】 MySQL数据库备份与恢复命令实战

mysql> show variables like ‘character_set%‘; +--------------------------+-------------------------------------------+ | Variable_name            | Value                                     | +--------------------------+-------------------------------------------+ | character_set_client     | utf8                                      | | character_set_connection | utf8                                      | | character_set_database   | utf8                                      | | character_set_filesystem | binary                                    | | character_set_results    | utf8                                      | | character_set_server     | utf8                                      | | character_set_system     | utf8                                      | | character_sets_dir       | /application/mysql-5.5.32/share/charsets/ | +--------------------------+—————————————————————+   【查看数据库对应的库的字符集】 mysql> show create database oldboy\G *************************** 1. row ***************************        Database: oldboy Create Database: CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */ 1 row in set (0.00 sec)

  【查看数据库表的结构】 mysql> show create table student\G *************************** 1. row ***************************        Table: student Create Table: CREATE TABLE `student` (   `id` int(11) DEFAULT NULL,   `name` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)   【备份数据库  命令】
[root@localhost ~]# mysqldump -uroot -p‘oldboy123‘ oldboy >/opt/mysql_bak.sql [root@localhost ~]# ll /opt/ -rw-r--r--. 1 root root 3221 Feb 16 21:02 mysql_bak.sql   【备份数据库  命令】 【检查备份的数据库是否正确】 【备份数据库同时检查备份的数据库是否正确】 【可以不操作，只是在检查的时候显示正常的】 [root@localhost ~]# mysqldump -uroot -p‘oldboy123‘ oldboy >/opt/mysql_bak.sql [root@localhost ~]# egrep -v "#|\*|--|^$" /opt/mysql_bak.sql DROP TABLE IF EXISTS `student`; CREATE TABLE `student` (   `id` int(11) DEFAULT NULL,   `name` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; LOCK TABLES `student` WRITE; INSERT INTO `student` VALUES (3,‘测试003‘),(1,‘测试001‘),(2,‘测试002‘),(4,‘测试004‘),(5,‘测试005‘); UNLOCK TABLES;   【备份的时候指定字符集备份（导出）】 【这样在检查导出的数据库的时候就不会出现乱码的情况，指定的字符集为数据库表的字符集】   【恢复备份的数据，同时检查恢复的数据】 [root@localhost /]# mysql -uroot -poldboy123 oldboy < /opt/mysql_bak.sql [root@localhost /]# mysql -uroot -poldboy123 -e "select * from oldboy.student"; +------+-----------+ | id   | name      | +------+-----------+ |    3 | 测试003   | |    1 | 测试001   | |    2 | 测试002   | |    4 | 测试004   | |    5 | 测试005   | +------+-----------+   【加-B 备份数据库】 [root@localhost ~]# mysqldump -uroot -poldboy123 -B oldboy >/opt/mysql_bak_b.sql [root@localhost ~]# egrep -v "#|\*|--|^$" /opt/mysql_bak_b.sql USE `oldboy`; DROP TABLE IF EXISTS `student`; CREATE TABLE `student` (   `id` int(11) DEFAULT NULL,   `name` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; LOCK TABLES `student` WRITE; INSERT INTO `student` VALUES (3,‘测试003‘),(1,‘测试001‘),(2,‘测试002‘),(4,‘测试004‘),(5,‘测试005‘); UNLOCK TABLES;       【加B 参数的作用是增加创建数据库和连接数据库的命令】 【以及利用加B 进行数据恢复】     【指定压缩命令压缩备份的MySQL数据】   [root@localhost ~]# mysqldump -uroot -poldboy123 -B oldboy|gzip >/opt/mysql_bak_b.sql.gz [root@localhost ~]# ll /opt/ -rw-r--r--. 1 root root 2058 Feb 16 22:48 mysql_bak_b.sql -rw-r--r--. 1 root root  771 Feb 16 22:57 mysql_bak_b.sql.gz     【数据库备份的过程重点】 1.导出数据的时候用-B 参数 2，用gzip对备份的数据压缩   5.1.3 mysqldump的工作原理 利用mysqldump 命令备份数据的过程，实际上就是把数据从MySQL库里以逻辑的sql语句的形式直接输出或者生成的备份的文件的过程     -B 参数说明:       【多库备份的步骤】
【1.查看数据库】 [root@localhost opt]# mysql -uroot -poldboy123 -e "show databases;" +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | oldboy             | | performance_schema | | t_a                | | test               | +——————————+   【2.过滤不需要的信息】 [root@localhost opt]# mysql -uroot -poldboy123 -e "show databases;"|grep -Evi "database|infor|perfor" mysql oldboy t_a test [root@lo     【3.替换要执行的语句】 [root@localhost opt]# mysql -uroot -poldboy123 -e "show databases;"|grep -Evi "database|infor|perfor"|sed ‘s#^#mysqldump -uroot -poldboy123 -B #g‘ mysqldump -uroot -poldboy123 -B mysql mysqldump -uroot -poldboy123 -B oldboy mysqldump -uroot -poldboy123 -B t_a mysqldump -uroot -poldboy123 -B test   【4.替换成指定的备份路径】 [root@localhost opt]# mysql -uroot -poldboy123 -e "show databases;"|grep -Evi "database|infor|perfor"|sed -r ‘s#^([a-z].*$)#mysqldump -uroot -poldboy123 -B \1 >/opt/\1#g‘ mysqldump -uroot -poldboy123 -B mysql >/opt/mysql mysqldump -uroot -poldboy123 -B oldboy >/opt/oldboy mysqldump -uroot -poldboy123 -B t_a >/opt/t_a mysqldump -uroot -poldboy123 -B test >/opt/test   【5.替换成指定备份的路径，压缩备份】 [root@localhost opt]# mysql -uroot -poldboy123 -e "show databases;"|grep -Evi "database|infor|perfor"|sed -r ‘s#^([a-z].*$)#mysqldump -uroot -poldboy123 -B \1|gzip >/opt/\1.sql.gz#g‘ mysqldump -uroot -poldboy123 -B mysql|gzip >/opt/mysql.sql.gz mysqldump -uroot -poldboy123 -B oldboy|gzip >/opt/oldboy.sql.gz mysqldump -uroot -poldboy123 -B t_a|gzip >/opt/t_a.sql.gz mysqldump -uroot -poldboy123 -B test|gzip >/opt/test.sql.gz     【6。执行命令】 [root@localhost opt]# mysql -uroot -poldboy123 -e "show databases;"|grep -Evi "database|infor|perfor"|sed -r ‘s#^([a-z].*$)#mysqldump -uroot -poldboy123 -B \1|gzip >/opt/\1.sql.gz#g‘|bash -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.   【7.检查备份的结果】 [root@localhost opt]# ll /opt/ total 160 -rw-r--r--. 1 root root 144306 Feb 16 23:23 mysql.sql.gz -rw-r--r--. 1 root root    769 Feb 16 23:23 oldboy.sql.gz drwxr-xr-x. 2 root root   4096 Mar 26  2015 rh -rw-r--r--. 1 root root    748 Feb 16 23:23 t_a.sql.gz -rw-r--r--. 1 root root    508 Feb 16 23:23 test.sql.gz   【7.忽略警告信息】 [root@localhost opt]# mysql -uroot -poldboy123 -e "show databases;"|grep -Evi "database|infor|perfor"|sed -r ‘s#^([a-z].*$)#mysqldump -uroot -poldboy123 --events -B \1|gzip >/opt/bak/\1.sql.gz#g‘|bash [root@localhost opt]#       【备份单个表】   [root@localhost mysql]# mysqldump -uroot -poldboy123 --compact oldboy student /*!40101 SET @saved_cs_client     = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `student` (   `id` int(11) DEFAULT NULL,   `name` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; INSERT INTO `student` VALUES (3,‘测试003‘),(1,‘测试001‘),(2,‘测试002‘),(4,‘测试004‘),(5,‘测试005‘);     【备份数据库的表结构（不包含数据）】     [root@localhost mysql]# mysqldump -uroot -poldboy123 --compact -B -d oldboy   CREATE DATABASE /*!32312 IF NOT EXISTS*/ `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */;   USE `oldboy`; /*!40101 SET @saved_cs_client     = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `student` (   `id` int(11) DEFAULT NULL,   `name` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; [root@localhost mysql]#     【指定库中的某一个表的结构进行备份】 [root@localhost mysql]# mysqldump -uroot -poldboy123 --compact -d oldboy student /*!40101 SET @saved_cs_client     = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `student` (   `id` int(11) DEFAULT NULL,   `name` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */;     【    -T  分开到处数据和结构】 【直接备份数据】 【-t 参数】 [root@localhost mysql]# mysqldump -uroot -poldboy123 --compact -t oldboy student INSERT INTO `student` VALUES (3,‘测试003‘),(1,‘测试001‘),(2,‘测试002‘),(4,‘测试004‘),(5,‘测试005‘);   【-A 】 整个备份数据库 -包括可和表 [root@localhost mysql]# mysqldump -uroot -poldboy123 -A -B --events|gzip >/opt/a.sql.gz [root@localhost mysql]# ll /opt/ total 308 -rw-r--r--. 1 root root 144664 Feb 17 01:45 a.sql.gz drwxr-xr-x. 2 root root   4096 Feb 16 23:25 bak   在   my.cnf     中打开log日志记录 log-bin=mysql-bin  会将MySQL中的所有更改记录下来   【MySQL 日志的路径】 [root@localhost data]# pwd /application/mysql/data ll -rw-rw----. 1 mysql mysql      541 Feb 14 18:33 mysql-bin.000001 -rw-rw----. 1 mysql mysql    11815 Feb 14 22:24 mysql-bin.000002 -rw-rw----. 1 mysql mysql     3557 Feb 16 22:51 mysql-bin.000003 -rw-rw----. 1 mysql mysql     1851 Feb 17 01:31 mysql-bin.000004 -rw-rw----. 1 mysql mysql       76 Feb 16 22:51 mysql-bin.index   【    -F  刷新binlog】   [root@localhost data]# mysqldump -uroot -poldboy123 -A -B -F --events|gzip >/opt/a.sql.gz   【    - -master-data=1    在备份的时候会自动记录binlog的当前位置】 [root@localhost data]# mysqldump -uroot -poldboy123 --master-data=1 --compact oldboy CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000008‘, MASTER_LOG_POS=107; /*!40101 SET @saved_cs_client     = @@character_set_client */;   【查看binlog记录】 mysqlbinlog   【总结：MySQL语句总结整理】 【    -T  分开到处数据和结构                            】 【    -t    直接备份数据                                      】 【    -A    备份所有库      】     【    -B    指定多个库  ，增加建库语句和use语句备份库                】 【    -d    只备份表            】    -d oldboy student 【    —compact  去掉注释，适合调试输出，生产不用。精简内容  一般做测试的时候使用】 【    --events    忽略警告信息                                  】 【    -F     刷新binlog日志  ，也就是会创建一个递增的新的日志文件    切割（刷新）binlog   】 【    - -master-data=1    增加binlog日志文件名以及对应的位置点    在备份的时候会自动记录binlog的当前位置】 【    -x   - - lock-all-tables    锁表】 【    -e  在数据库外面执行sql语句    】       【生产场景备份】 myisam 备份命令 mysqldump -uroot -poldboy123 -A -B —master-data=2 -x --events|gzip >/opt/all.sql.gz   innodb 备份命令：推荐使用的 mysqldump -uroot -poldboy123 -A -B —master-data=2 -x —events —single-transaction|gzip >/opt/all.sql.gz 【专业DB 备份操作】     5.2 恢复数据库实践   【恢复命令  source】 mysql> source /opt/mysql.b.sql Query OK, 1 row affected (0.00 sec)
【解压备份的压缩文件】 gzip -d msyql.bak.sql.gz   将备份出来的所有的库，恢复到数据库中 [root@localhost data]# for dbname in `ls *.sql|sed ‘s#_bak.sql##g‘`;do mysql -uroot -poldboy123 < ${dbname}_bak.sql;done       【MySQL 系统一些命令】 【查看当前链接数据库的用户以及数量】 [root@localhost data]# mysql -uroot -poldboy123 -e "show processlist;" +----+------+-----------+------+---------+-------+-------+------------------+ | Id | User | Host      | db   | Command | Time  | State | Info             | +----+------+-----------+------+---------+-------+-------+------------------+ |  3 | root | localhost | NULL | Sleep   | 16551 |       | NULL             | | 58 | root | localhost | NULL | Query   |     0 | NULL  | show processlist |    

【整 1.2.3】 MySQL数据库备份与恢复命令实战

标签：unlock   create   image   技术   nod   mysql-5.5   pos   情况   utf8