备份单个数据库
MySQL数据库自带一个很好的备份命令，就是mysqldump。
基本语法：mysqldump -u 用户名 -p 数据库名 > 备份的文件名
示例
1 备份一个库
[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | db_3306 | | mysql | | performance_schema | | test | +--------------------+ 7 rows in set (0.00 sec) # 备份 [root@localhost ~]# mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock db1 >/opt/mysql_db1_bak.sql # 查看 [root@localhost ~]# egrep -v "#|\*|--|^$" /opt/mysql_db1_bak.sql DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1; LOCK TABLES `t1` WRITE; INSERT INTO `t1` VALUES (1,'a'),(2,'b'),(3,'c'),(4,'e'); UNLOCK TABLES; [root@localhost ~]#
删除db1中的表进行测试
[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock -e "drop table db1.t1;"
用备份文件进行恢复
[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock db1 </opt/mysql_db1_bak.sql Warning: Using a password on the command line interface can be insecure. [root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock -e "select * from db1.t1;" Warning: Using a password on the command line interface can be insecure. +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | e | +----+------+ [root@localhost ~]#
加 -B参数，增加创建数据库(create database)和连接数据库(use db)的命令。
[root@localhost ~]# mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock -B db1 >/opt/mysql_db1_b_bak.sql [root@localhost opt]# diff mysql_db1_bak.sql mysql_db1_b_bak.sql 18a19,26 > -- Current Database: `db1` > -- > > CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db1` /*!40100 DEFAULT CHARACTER SET latin1 */; > > USE `db1`; > > -- 51c59 < -- Dump completed on 2018-11-29 8:26:33 --- > -- Dump completed on 2018-11-29 8:45:31 [root@localhost opt]#
利用-B的备份文件恢复
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | db_3306 | | mysql | | performance_schema | | test | +--------------------+ 7 rows in set (0.00 sec) mysql> drop database db1; Query OK, 1 row affected (0.37 sec) # 备份恢复 [root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock db1 </opt/mysql_db1_bak.sql Warning: Using a password on the command line interface can be insecure. ERROR 1049 (42000): Unknown database 'db1' [root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock </opt/mysql_db1_b_bak.sql Warning: Using a password on the command line interface can be insecure. [root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock -e "select * from db1.t1;" Warning: Using a password on the command line interface can be insecure. +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | e | +----+------+ [root@localhost ~]#
压缩备份|gzip
[root@localhost ~]# mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock -B db1|gzip >/opt/mysql_db1_bak_gz.sql.gz Warning: Using a password on the command line interface can be insecure. [root@localhost ~]# cd /opt/ [root@localhost opt]# ll total 16 -rw-r--r--. 1 root root 779 Nov 29 09:01 mysql_db1_bak_gz.sql.gz -rw-r--r--. 1 root root 1884 Nov 29 08:26 mysql_db1_bak.sql -rw-r--r--. 1 root root 2020 Nov 29 08:45 mysql_db1_b_bak.sql
压缩备份恢复 命令gunzip
gunzip < /opt/mysql_db1_bak_gz.sql.gz | mysql -uroot -p123456 -S /tmp/mysql_3306.sock
1.到处数据用-B参数
2.用gzip对备份数据进行压缩
mysqldump工作原理
逻辑备份：利用mysqldump命令备份数据过程，实际上就是把数据从mysql库以逻辑的sql语句形式直接输出或者生产备份文件的过程。
备份多个库
查看mysqldump参数：mysqldump --help
-B 参数，表示连接多个库，并且增加use db和create database db的信息。
[root@localhost ~]# mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock -B db1 db2|gzip >/opt/mysql_mul.sql.gz
删除db1和db2库进行测试
mysql> drop database db1; Query OK, 1 row affected (0.01 sec) mysql> drop database db2; Query OK, 2 rows affected (0.07 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db_3306 | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) # 恢复 [root@localhost ~]# gunzip < /opt/mysql_mul.sql.gz | mysql -uroot -p123456 -S /tmp/mysql_3306.sock # 登陆查看 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | db_3306 | | mysql | | performance_schema | | test | +--------------------+
分库备份
分库备份就是执行一个备份语句备份一个库，如果数据库里面有多个库，就执行多条相同的备份单个库的备份语句就可以备份多个库了，注意每个库都可以用对应备份的库作为库名，结果加.sql。
语法：
mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock -B db1 >/opt/mysql_db1_bak.sql
mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock -B db2 >/opt/mysql_db2_bak.sql
...
分库备份意义：
企业数据库里有多个库，出问题时可能是某一个库，备份时把所有库备份成一个数据文件，恢复某个库的数据比较麻烦。
备份表
语法：mysqldump -u 用户名 -p 数据库名 表名 > 备份的文件名
单表备份：
[root@localhost ~]# mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock db1 t1 >/opt/table1.sql # 删除表进行测试 mysql> drop table t1; Query OK, 0 rows affected (0.05 sec) # 恢复 [root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock db1</opt/table1.sql
备份多个表：
语法：mysqldump -u 用户名 -p 数据库名 表名1 表名2 > 备份的文件名
需求：一个库里有大表小表，有时只需要恢复小表，上述的多表备份很难拆分。
解决：和分库的思想一样，每执行一条语句备份一个表，生成不同的数据文件。
mysqldump -u root -p db1 t1 > db1_t1.sql
mysqldump -u root -p db1 t2 > db1_t2.sql
...
分表备份缺点：文件多，啐。
1.备一个完整全备，再做一个分库分表备份。
2.脚本批量恢复多个SQL文件。
备份表结构或数据
# 备份表结构加 -d
[root@localhost ~]# mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock --compact -d db1 Warning: Using a password on the command line interface can be insecure. /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `t2` ( `id` int(11) DEFAULT NULL, `name` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; [root@localhost ~]#
备份数据 加 -t 参数
[root@localhost ~]# mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock --compact -t db1 t1 Warning: Using a password on the command line interface can be insecure. INSERT INTO `t1` VALUES (1,'a'),(2,'b'),(3,'c'),(4,'e'); [root@localhost ~]#
备份数据库所有数据 参数 -A -B
[root@localhost ~]# mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock --compact -A -B --events|gzip >/opt/a_b.sql.gz
打开log-bin是mysql的增量恢复
# bin-log文件位置 [root@localhost log]# cd /data/mysql/mysql_3306/log/ [root@localhost log]# ll total 84 -rw-rw----. 1 mysql mysql 41065 Nov 29 08:13 error.log -rw-rw----. 1 mysql mysql 143 Nov 27 00:46 mysql3306_bin.000001 -rw-rw----. 1 mysql mysql 143 Nov 27 00:47 mysql3306_bin.000002 -rw-rw----. 1 mysql mysql 806 Nov 27 11:33 mysql3306_bin.000003 -rw-rw----. 1 mysql mysql 1203 Nov 27 16:01 mysql3306_bin.000004 -rw-rw----. 1 mysql mysql 2809 Nov 28 15:25 mysql3306_bin.000005 -rw-rw----. 1 mysql mysql 6137 Nov 29 12:02 mysql3306_bin.000006 -rw-rw----. 1 mysql mysql 288 Nov 29 08:13 mysql3306_bin.index -rw-rw----. 1 mysql mysql 1008 Nov 29 08:13 slow.log [root@localhost log]#
使用-F切割bin-log，刷新bin-log参数
[root@localhost ~]# mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock --compact -A -B -F --events|gzip >/opt/a_b.sql.gz
--master-data=1 自动找到bin-log的位置
--master-data 找到全备的点：mysql3306_bin.000006
[root@localhost log]# mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock --master-data=1 --compact db1 Warning: Using a password on the command line interface can be insecure. CHANGE MASTER TO MASTER_LOG_FILE='mysql3306_bin.000006', MASTER_LOG_POS=6137; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; INSERT INTO `t1` VALUES (1,'a'),(2,'b'),(3,'c'),(4,'e'); /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `t2` ( `id` int(11) DEFAULT NULL, `name` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; [root@localhost log]#
mysqldump关键参数
mysqldump --help|less
1 -B 指定多个库，增加建库语句和use语句
2 --compact 去掉注释，适合调试输出
3 -A 所有库
4 -F 刷新binlog日志
5 -x，--lock-all-tables 锁表
6 --master-data 增加binlog日志文件及对应的位置点
7 -l ,--lock-tables 只读锁表
8 -d 只备份表结构
9 -t 只备份数据
10 --single-transaction 适合InnoDB事务数据库备份
工作原理： 设定本次会话的隔离级别：REPEATABLE READ，以确保本次会话（dump）时，不会看到其它会话提交的数据。
分引擎备份
myisam引擎：
mysqldump -uroot -p123456 -A -B --master-data=2 -x|gzip > /opt/all.sql.gz
mysqldump --user=root --all-databases --flush-privileges --lock-all-tables --master-data=1 --flush-logs --triggers --routines --events --hex-blob > $backup_dir/full_dump_$backup_timestamp.sql
InnoDB引擎：
mysqldump -uroot -p123456 -A -B --master-data=2 --single-transaction|gzip > /opt/all.sql.gz
mysqldump --user=root --all-databases --flush-privileges --single-transaction --master-data=1 --flush-logs --triggers --routines --events --hex-blob >$backup_dir/full_dump_$backup_timestamp.sql
参数使用
-e 参数可以不进入mysql查看信息
[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock -e "select * from db1.t1;" Warning: Using a password on the command line interface can be insecure. +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | e | +----+------+ [root@localhost ~]#
show processlist/ show full processlist
[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock -e "show processlist;" Warning: Using a password on the command line interface can be insecure. +----+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+------------------+ | 48 | root | localhost | NULL | Query | 0 | init | show processlist | +----+------+-----------+------+---------+------+-------+------------------+
show variables
[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock -e "show variables;"|grep log_bin Warning: Using a password on the command line interface can be insecure. log_bin ON log_bin_basename /data/mysql/mysql_3306/log/mysql3306_bin log_bin_index /data/mysql/mysql_3306/log/mysql3306_bin.index log_bin_trust_function_creators OFF log_bin_use_v1_row_events OFF sql_log_bin ON [root@localhost ~]#
show global status --计数器 mysql调优查看
[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock -e "show status;"|grep sel Warning: Using a password on the command line interface can be insecure. Com_insert_select 0 Com_replace_select 0 Com_select 1 Connection_errors_select 0 [root@localhost ~]#
更改数据库参数不重启生效
# 不重启都生效 mysql> show variables like 'key_buffer%'; +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | key_buffer_size | 8388608 | +-----------------+---------+ 1 row in set (0.00 sec) mysql> set global key_buffer_size=2M; # 在配置文件也修改，是否重启都生效
1.show status 查看当前会话的数据库状态信息。
2.show global status 查看整个数据库运行状态信息，分析并做好监控。
3.show processlist 查看正在执行的sql语句，不全。
4.show full processlist 查看正在执行的完整sql语句，完整显示。
5.set global key_buffer_size=1M 不重启数据库直接生效，重启后失效。
6.show variables 查看数据库的参数信息，如my.cnf里参数的生效情况。
恢复实践
登陆到数据库里面使用source进行恢复
[root@localhost opt]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | db_3306 | | mysql | | performance_schema | | test | +--------------------+ 7 rows in set (0.00 sec) mysql> mysql> drop database db1; Query OK, 1 row affected (0.01 sec) mysql> show databases; mysql> system ls /opt a_b.sql.gz mysql_db1_bak.sql mysql_mul.sql.gz table1.sql mysql_db1_bak_gz.sql.gz mysql_db1_b_bak.sql rh mysql> source /opt/mysql_db1_b_bak.sql mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | db_3306 | | mysql | | performance_schema | | test | +--------------------+ 7 rows in set (0.00 sec) mysql> select * from db1.t1; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | e | +----+------+ 4 rows in set (0.00 sec)
source恢复
本站文章如无特殊说明，均为本站原创，如若转载，请注明出处：MySQL备份 - Python技术站
微信扫一扫 
支付宝扫一扫 
