本章内容：
- 主从复制
- 简介原理
- Mysql主从同步脚本部署
- 读写分离
- 如果主宕机了，怎么办？
- 双主的情况
- MySQL 备份及恢复方案
- 备份单个及多个数据库
- mysqldump 的常用参数
- 如何增量恢复呢？
- 增量恢复的必要条件
- 生产环境 mysqldump 备份命令
- 恢复
1.MySQL主从复制
1.1简介即原理
我们为什么要用主从复制？
主从复制目的：
可以做数据库的实时备份，保证数据的完整性；
可做读写分离，主服务器只管写，从服务器只管读，这样可以提升整体性能。
原理图：
1.2MySQL主从同步脚本部署
mysql多实例一键主从同步，3306同步到3307
注意：
检查主从配置文件server-id跟log-bin
[root@oldboy opt]# egrep "log-bin|server-id" /data/3306/my.cnf log-bin = /data/3306/mysql-bin server-id = 1 [root@oldboy opt]# egrep "log-bin|server-id" /data/3307/my.cnf #log-bin = /data/3307/mysql-bin server-id = 3
1.2.1备份和show master日志位置
[root@data-1-1 tools]# cat auto_mysql3306_bak.sh #!/bin/sh #this scripts is created by zsq #zsq trainning QQ :493939840 MYUSER=root MYPASS="oldboy123" MYSOCK=/data/3306/mysql.sock [ ! -d /backup ] && mkdir -p /backup MAIN_PATH=/backup DATA_PATH=/backup LOG_FILE=${DATA_PATH}/mysqllogs_`date +%F`.log DATA_FILE=${DATA_PATH}/mysql_backup_`date +%F`.sql.gz MYSQL_PATH=/application/mysql/bin MYSQL_CMD="$MYSQL_PATH/mysql -u$MYUSER -p$MYPASS -S $MYSOCK" MYSQL_DUMP="$MYSQL_PATH/mysqldump -u$MYUSER -p$MYPASS --events --ignore-table=mysql.events -S $MYSOCK -A -B --flush-logs --single-transaction -e" $MYSQL_CMD -e "grant replication slave on *.* to oldboy@'192.168.179.%' identified by '123456';" $MYSQL_CMD -e "flush tables with read lock;" echo "-----show master status result-----" >>$LOG_FILE $MYSQL_CMD -e "show master status;" >>$LOG_FILE ${MYSQL_DUMP} | gzip > $DATA_FILE $MYSQL_CMD -e "unlock tables;" echo "ok"
1.2.2执行备份脚本
[root@data-1-1 tools]# sh auto_mysql3306_bak.sh -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly. auto_mysql3306_bak.sh: line 26: mail: command not found
提示：-- Warning: Skipping
因为mysqldump默认是不备份事件表的,只有加了--events 才会解决 加上--events --ignore-table=mysql.events参数即可
示例
[root@data-1-1 ~]# /application/mysql/bin/mysqldump -uroot -poldboy123 --events --ignore-table=mysql.events -S /data/3307/mysql.sock -A -B --flush-logs --single-transaction -e |gzip >/tmp/mysql_backup_`date +%F`.sql.gz [root@data-1-1 tools]# ls /backup/ mysql_backup_2016-09-02.sql mysqllogs_2016-09-02.log [root@data-1-1 tools]# cat /backup/mysqllogs_2016-09-02.log -----show master status result----- File Position Binlog_Do_DB Binlog_Ignore_DB mysql-bin.000009 261
1.2.3执行同步脚本
[root@data-1-1 tools]# cat auto_mysql3307_slave.sh #!/bin/sh ################################################ #this scripts is created by zsq #zsq trainning QQ :493939840 ################################################ MYUSER=root MYPASS="oldboy123" MYSOCK=/data/3307/mysql.sock MAIN_PATH=/backup DATA_PATH=/backup LOG_FILE=${DATA_PATH}/mysqllogs_`date +%F`.log DATA_FILE=${DATA_PATH}/mysql_backup_`date +%F`.sql.gz MYSQL_PATH=/application/mysql/bin MYSQL_CMD="$MYSQL_PATH/mysql -u$MYUSER -p$MYPASS -S $MYSOCK" #recover cd ${DATA_PATH} gzip -d mysql_backup_`date +%F`.sql.gz $MYSQL_CMD < mysql_backup_`date +%F`.sql #config slave cat |$MYSQL_CMD<< EOF CHANGE MASTER TO MASTER_HOST='192.168.179.186', #注意这是主的节点IP MASTER_PORT=3306, MASTER_USER='oldboy', MASTER_PASSWORD='123456', MASTER_LOG_FILE="`tail -1 $LOG_FILE|cut -f1`", MASTER_LOG_POS=`tail -1 $LOG_FILE|cut -f2`; EOF $MYSQL_CMD -e "start slave;" $MYSQL_CMD -e "show slave statusG"|egrep "IO_Running|SQL_Running"
1.3查看状态
[root@data-1-1 backup]# /application/mysql/bin/mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "show slave statusG"|egrep "IO_Running|SQL_Running" Slave_IO_Running: Yes Slave_SQL_Running: Yes
1.4测试，登陆主数据库，备数据库查看
[root@data-1-1 tools]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock mysql> create database sisi; Query OK, 1 row affected (0.00 sec) mysql> use sisi Database changed mysql> create table t(id int); Query OK, 0 rows affected (0.31 sec) mysql> insert into t values(01); Query OK, 1 row affected (0.04 sec) mysql> select * from t; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.04 sec) [root@data-1-1 tools]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock mysql> select * from sisi.t; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec)
2.配置mysql从库级联复制
由于前一次我们只配置了主库3306、从库3307，这一次我们还需要添加从库3308
1、下面让我们来配置mysql 3308的多实例启动方法：
mkdir -p /data/3308/data cp /data/3306/my.cnf /data/3308/ cp /data/3306/mysql /data/3308/ sed -i 's/3306/3308/g' /data/3308/my.cnf sed -i 's/server-id = 1/server-id = 9/g' /data/3308/my.cnf sed -i 's/3306/3308/g' /data/3308/mysql chown -R mysql:mysql /data/3308 chmod 700 /data/3308/mysql cd /application/mysql/scripts ./mysql_install_db --datadir=/data/3308/data --basedir=/application/mysql --user=mysql chown -R mysql:mysql /data/3308 egrep "server-id|log-bin" /data/3308/my.cnf /data/3308/mysql start sleep 5 netstat -lnt|grep 3308 mysqladmin -u root password 'zsq3308' -S /data/3308/mysql.sock #初始化3308数据库密码
2.1查看mysql 3306 3307 3308各个服务是否开启
[root@zsq scripts]# netstat -lntup|grep 330 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3600/mysqld tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 4332/mysqld tcp 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 5251/mysqld
配置mysql从库级联复制
环境是：3306主库 3307从库 3308从库
做了主库3306到从库3307，所以现在我们要实现的需求是，当主库3306产生bin_log，发给从库，从库3307产生的bin_log文件发送给其他从库3308。
2.1.1）开启从库3307的log-bin日志文件
sed -i 's@#log-bin = /data/3307/mysql-bin@log-bin = /data/3307/mysql-bin@g' /data/3307/my.cnf
2.1.2）在3307从库配置文件my.cnf，[mysqld]模块添加 如下内容
log-bin = /data/3307/mysql-bin log-slave-updates = 1 expire_logs_days = 7 #重启数据库3307 [root@zsq data]# /data/3307/mysql stop Stoping MySQL... [root@zsq data]# /data/3307/mysql start Starting MySQL... 如果现下面的错误的时候 [root@zsq data]# /data/3307/mysql stop Stoping MySQL... /application/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: YES)' 那是因为我们在做单台主从复制的时候，是将主服务器整个包导入到从库3307的，所以修改从库3307的启动文件mysqld sed -i 's/zsq3307/zsq3306/g' /data/3307/mysql
2.1.3）登陆从库3307，查看log_slave_updates状态是否开启
mysql> show variables like "log_slave_updates"; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | log_slave_updates | ON | +-------------------+-------+ 1 row in set (0.00 sec)
2.1.4）通过mysqldump导出从库3307数据文件
mysqldump -uroot -pzsq3306 -S /data/3307/mysql.sock -A --events -B -F -x --master-data=1|gzip > /opt/zsq.sql.gz #--master-data=1,表示在zsq.sql文件中将取消注释“CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107;
2.1.5）解压数据库，并导入从库3308
cd /opt/ gzip -d zsq.sql.gz mysql -uroot -pzsq3308 -S /data/3308/mysql.sock <zsq.sql
2.1.6) 登录从数据库3308
mysql -uroot -pzsq3308 -S /data/3308/mysql.sock mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.102', MASTER_PORT=3307, MASTER_USER='rep', MASTER_PASSWORD='zsq123'; mysql> start slave; #开启从库3307到从库3308同步开关 mysql> show slave statusG; #查看从库3308状态 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.179.102 Master_User: rep Master_Port: 3307 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 188 Relay_Log_File: relay-bin.000007 Relay_Log_Pos: 334 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 188 Relay_Log_Space: 527 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 3 1 row in set (0.00 sec) ERROR: No query specified
使用此种方法也能快速查看从库mysql的状态
[root@zsq data]# mysql -uroot -p'zsq3307' -S /data/3307/mysql.sock -e "show slave statusG;"|egrep -i "_running|_Behind" Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0
2.1.7）登录主库3306，删除测试数据库
[root@zsq opt]# mysql -uroot -plx3306 -S /data/3306/mysql.sock mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | amoeba_test | | amoeba_test1 | | amoeba_test2 | | mysql | | performance_schema | | sisi1 | | test | +--------------------+ 8 rows in set (0.00 sec) mysql> drop database sisi1; Query OK, 0 rows affected (0.00 sec) [root@zsq opt]# mysql -uroot -plx3306 -S /data/3307/mysql.sock #3307查看 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | amoeba_test | | amoeba_test1 | | amoeba_test2 | | mysql | | performance_schema | | test | +--------------------+ 7 rows in set (0.01 sec) [root@zsq ~]# mysql -uroot -plx3308 -S /data/3308/mysql.sock #3308查看 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | amoeba_test | | amoeba_test1 | | amoeba_test2 | | mysql | | performance_schema | | test | +--------------------+ 7 rows in set (0.00 sec)
发现数据库都被删除了，至此mysql级联复制配置完毕
2.1.8. 多实例mysql 修改密码
mysql> select user,host,password from mysql.user; mysql> update mysql.user set password=PASSWORD('sisi') where user='root'; mysql> flush privileges; 配置文件不指定密码重启会报错 [root@oldboy /]# cat /data/3308/mysql|grep "mysql_pwd=" mysql_pwd="sisi
3.mysql主主复制
应用场景：高并发场景，使用双主双写，慎用！
注意： ID会冲突
解决 ID 冲突问题
方法一： 表的id自增，让主A 写1，3，5；主B 写2，4，6；
方法二：表的id不自增，通过web端程序去seq取id，写入双主。
环境：主库3306 ，从库 3307
由于我们已经做了主库3306到从库3307，现在我们需要将从库3307变为主库，将3306作为从库
具体操作步骤：
3.1编辑数据库配置文件
[root@zsq 3306]# cd /data/3306 [root@zsq 3306]# vim my.cnf ……省略…… [mysqld] # 以下内容加在[mysqld]下面 #________m-m m1 start________ auto_increment_increment = 2 #自增ID的间隔 auto_increment_offset = 1 #ID的初始位置 log-slave-updates = 1 log-bin = /data/3306/mysql-bin expire_logs_days = 7 #________m-m m1 end________ ……省略…… 重启mysql [root@zsq 3306]# ./mysql stop Stoping MySQL... [root@zsq 3306]# ./mysql start Starting MySQL... [root@zsq 3306]# cd /data/3307 [root@zsq 3307]# vim my.cnf ……省略…… [mysqld] # 以下内容加在[mysqld]下面 #________m-m m1 start________ auto_increment_increment = 2 #自增ID的间隔 auto_increment_offset = 2 #ID的初始位置 log-slave-updates = 1 log-bin = /data/3307/mysql-bin expire_logs_days = 7 #________m-m m1 end________ ……省略…… 重启mysql [root@zsq 3307]# ./mysql stop Stoping MySQL... [root@zsq 3307]# ./mysql start Starting MySQL...
3.2导出3307数据库数据
mysqldump -uroot -plx3306 -S /data/3307/mysql.sock -A --events -B -F -x --master-data=1|gzip > /opt/$(date +%F).sql.gz 1）解压并将数据导入到3306 gzip -d 2016-06-10.sql mysql -uroot -plx3306 -S /data/3306/mysql.sock < 2016-06-10.sql 2）登录主数据库3306 mysql -uroot -plx3306 -S /data/3306/mysql.sock mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.102', MASTER_USER='rep', MASTER_PORT=3307, MASTER_PASSWORD='lx123'; mysql> start slave; 3）查看从库3306状态 mysql> show slave statusG; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.179.102 Master_User: rep Master_Port: 3307 Connect_Retry: 60 Master_Log_File: mysql-bin.000007 Read_Master_Log_Pos: 1986 Relay_Log_File: relay-bin.000005 Relay_Log_Pos: 728 Relay_Master_Log_File: mysql-bin.000007 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1986 Relay_Log_Space: 921 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 3 1 row in set (0.00 sec) ERROR: No query specified 4）在数据库3306创建数据库students mysql -uroot -plx3306 -S /data/3306/mysql.sock mysql> create database students; mysql> use students; 5）创建表t1，并插入内容 mysql> CREATE TABLE `t1` ( `id` bigint(12) NOT NULL auto_increment, `name` varchar(12) NOT NULL, PRIMARY KEY (`id`) ); mysql> insert into t1(name) values("oldgirl"); mysql> insert into t1(name) values("oldboy"); mysql> select * from t1; +----+---------+ | id | name | +----+---------+ | 1 | oldgirl | | 3 | oldboy | +----+---------+ 结果查看到内容是按照ID号，1 3 ……进行增长 6）登录到3307数据库 [root@zsq opt]# mysql -uroot -plx3306 -S /data/3307/mysql.sock mysql> use students; mysql> select * from t1; +----+---------+ | id | name | +----+---------+ | 1 | oldgirl | | 3 | oldboy | +----+---------+ mysql> insert into t1(name) values("lx"); mysql> insert into t1(name) values("swj"); mysql> select * from t1; +----+---------+ | id | name | +----+---------+ | 1 | oldgirl | | 3 | oldboy | | 4 | lx | | 6 | swj | +----+---------+ 4 rows in set (0.00 sec) 查看到数据库3307的ID是按照偶数进行递增的 7）登陆3308 查看 ，发现数据已经都同步过来了 mysql -uroot -plx3308 -S /data/3308/mysql.sock mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | amoeba_test | | amoeba_test1 | | amoeba_test2 | | mysql | | performance_schema | | students | | test | +--------------------+ 8 rows in set (0.00 sec) mysql> use students; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from t1; +----+---------+ | id | name | +----+---------+ | 1 | oldgirl | | 3 | oldboy | | 4 | lx | | 6 | swj | +----+---------+ 4 rows in set (0.00 sec) 8)3308的配置文件 [root@oldboy 3308]# vim my.cnf [client] port = 3308 socket = /data/3308/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql port = 3308 socket = /data/3308/mysql.sock basedir = /application/mysql datadir = /data/3308/data open_files_limit = 1024 back_log = 600 max_connections = 800 max_connect_errors = 3000 table_cache = 614 external-locking = FALSE max_allowed_packet =8M sort_buffer_size = 1M join_buffer_size = 1M thread_cache_size = 100 thread_concurrency = 2 query_cache_size = 2M query_cache_limit = 1M query_cache_min_res_unit = 2k #default_table_type = InnoDB thread_stack = 192K #transaction_isolation = READ-COMMITTED tmp_table_size = 2M max_heap_table_size = 2M long_query_time = 1 #log_long_format #log-error = /data/3308/error.log #log-slow-queries = /data/3308/slow.log pid-file = /data/3308/mysql.pid log-bin = /data/3308/mysql-bin relay-log = /data/3308/relay-bin relay-log-info-file = /data/3308/relay-log.info binlog_cache_size = 1M max_binlog_cache_size = 1M max_binlog_size = 2M expire_logs_days = 7 key_buffer_size = 16M read_buffer_size = 1M read_rnd_buffer_size = 1M bulk_insert_buffer_size = 1M #myisam_sort_buffer_size = 1M #myisam_max_sort_file_size = 10G #myisam_max_extra_sort_file_size = 10G #myisam_repair_threads = 1 #myisam_recover lower_case_table_names = 1 skip-name-resolve slave-skip-errors = 1032,1062 replicate-ignore-db=mysql server-id = 9 innodb_additional_mem_pool_size = 4M innodb_buffer_pool_size = 32M innodb_data_file_path = ibdata1:128M:autoextend innodb_file_io_threads = 4 innodb_thread_concurrency = 8 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 4M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 [mysqldump] quick max_allowed_packet = 2M [mysqld_safe] log-error=/data/3308/mysql_oldboy3308.err pid-file=/data/3308/mysqld.pid
3.3主从复制故障处理
1. 当从库复制遇到错误时，比如报错“要创建的数据库已存在”
解决方案： 让从库跳过这一步操作，继续执行其它的操作
方法一： 命令行实现，跳过这一步； mysql> stop slave; mysql> set global sql_slave_skip_counter =1; mysql> start slave; 方法二： 配置文件中，指定忽略的错误； [root@MySQL opt]# grep slave-skip /data/3308/my.cnf slave-skip-errors = 1032,1062
4.企业场景一主多从宕机从库切换主
（分两种一个是数据库宕机，一个是服务宕机）
4.1 模拟主库宕机
root@oldboy 3307]# /data/3306/mysql stop Stoping MySQL... [root@oldboy 3307]# netstat -ntpl|grep 3306 登陆从库，查看从库的线程更新状态 [root@oldboy 3307]# mysql -uroot -plx3308 -S /data/3308/mysql.sock mysql> show processlistG *************************** 1. row *************************** Id: 5 User: system user Host: db: NULL Command: Connect Time: 877066 State: Waiting for master to send event #IO线程 Info: NULL *************************** 2. row *************************** Id: 6 User: system user Host: db: NULL Command: Connect Time: 3855 State: Slave has read all relay log; waiting for the slave I/O thread to update it #SQL线程 ，说明跟主库复制是最新的 Info: NULL *************************** 3. row *************************** Id: 16 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: show processlist 3 rows in set (0.00 sec) 4.2. 查看所有slave ，看哪个binlog大，哪个大哪个数据最新、 #. 半同步的状态，就不用选了，直接就是它 （如果主库宕机，服务还能起来，把主库binlog补全） [root@oldboy 3307]# cat /data/3307/data/master.info 18 mysql-bin.000004 530740 192.168.179.102 rep lx123 3306 60 0 0 1800.000 0 [root@oldboy 3307]# cat /data/3308/data/master.info 18 mysql-bin.000008 107 192.168.179.102 rep lx123 3307 60 0 0 1800.000 0 4.3. 确保所有relay log全部更新完毕 mysql> stop slave io_thread; mysql> show processlistG #直到看到State: Slave has read all relay log;表示从库更新都执行完毕 mysql> quit 4.4. 进入到数据库目录，删除master.info relay-log.info cd /data/3308/data [root@oldboy data]# rm -rf master.info #relay-log有就删除 检查授权表，read-only等参数 # read-only防止数据写从库的参数 mysql> show grants for rep@'192.168.179.%'; +----------------------------------------------------------------------------------------------------------------------------+ | Grants for rep@192.168.179.% | +----------------------------------------------------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.179.%' IDENTIFIED BY PASSWORD '*4F567C322C9F749E6278E501EC4F3E80EBF7F064' | +----------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 4.5. 3308 提升为主库 log-bin = /data/3308/mysql-bin 如果存在log-slave-updates read-only等一定注释它。 [root@oldboy 3308]# /data/3308/mysql restart mysql> stop slave; mysql> CHANGE MASTER TO MASTER_HOST='192.168.179.102', MASTER_USER='rep', MASTER_PORT=3308, MASTER_PASSWORD='lx123'; #如果不是多实例，CHANGE MASTER TO MASTER_HOST='192.168.179.102', 修改下IP地址即可，用户名跟密码是一样的 #如果是多实例，修过下端口 #如果不同步要指定位置点 4.6.修改web程序文件指向新的主库 4.7. 修理坏的主库，完成后作为从库使用 4.8. mysql 5.5以上才支持半同步半同步下的一主多从恢复，直接对设置半同步的从库确定为主库 一主多从主库宕机通过master.info确定新主库 让某一个稳定从库和主库完全一致，即主库和这个从库更新数据完毕，在返回给用户更新成功 优点： 1.确保至少一个从库和主库数据一致 缺点： 1.主从之间网络延迟，或者从库有问题的时候，用户体验很差，当然可以设置超时时间，10秒 4.9.从库slave down机 恢复方法：机器宕机 重新做slave，直接灌数据 服务宕机，还能启动服务，停止主从开关，补全binglog 开启主从即可
5.MySQL的备份与恢复
5.1备份单个数据库
5.1.1最基础的备份单个数据库
1> 语法：mysqldump –u 用户名 –p 数据库名> 备份的数据库名 2> 备份nick_defailt数据库，查看内容。 [root@localhost ~]# mysqldump -uroot -p -B nick_defailt >/opt/mysql_nick_defailt.bak Enter password: [root@localhost ~]# egrep -v "#|*|--|^$" /opt/mysql_nick_defailt.bak DROP TABLE IF EXISTS `oldsuo`; CREATE TABLE `oldsuo` ( `id` int(4) NOT NULL, `name` char(20) NOT NULL, `age` tinyint(2) NOT NULL DEFAULT '0', `dept` varchar(16) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; LOCK TABLES `oldsuo` WRITE; INSERT INTO `oldsuo` VALUES (2,'ç´¢å®',0,NULL),(3,'索尼',0,NULL),(4,'底底',0,NULL); UNLOCK TABLES; DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `qq` varchar(15) DEFAULT NULL, `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, `suo` int(4) DEFAULT NULL, `age` tinyint(2) NOT NULL DEFAULT '0', `dept` varchar(16) DEFAULT NULL, `sex` char(4) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_name` (`name`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1; LOCK TABLES `student` WRITE; INSERT INTO `student` VALUES (NULL,2,'oldsuo',NULL,0,NULL,NULL),(NULL,3,'kangknag',NULL,0,NULL,NULL),(NULL,4,'kangkang',NULL,0,NULL,NULL),(NULL,5,'oldsuo',NULL,0,NULL,NULL),(NULL,6,'kangknag',NULL,0,NULL,NULL),(NULL,7,'kangkang',NULL,0,NULL,NULL); UNLOCK TABLES; 3> 启用压缩备份数据库 [root@localhost~]#mysqldump -uroot -p -B nick_defailt|gzip>/opt/mysql_nick_defailt.bak.gz Enter password: [root@localhost ~]# ll /opt/ 总用量 28 -rw-r--r--. 1 root root 2109 10月 24 16:36 data_bak.sq2 -rw-r--r--. 1 root root 2109 10月 24 16:36 data_bak.sql -rw-r--r--. 1 root root 1002 10月 27 11:55 mysql_nick_defailt.bak -rw-r--r--. 1 root root 1002 10月 27 11:56 mysql_nick_defailt.bak.gz -rw-r--r--. 1 root root 3201 10月 27 11:46 mysql_nick_defailt_B.bak drwxr-xr-x. 2 root root 4096 11月 22 2013 rh -rw-r--r--. 1 root root 1396 10月 24 16:11 student_bak.sql 4> 恢复nick_defailt数据库 [root@localhost ~]# mysql -uroot -p nick_defailt </opt/mysql_nick_defailt.bak Enter password: #加-B恢复方法 [root@localhost ~]# mysql -uroot -p </opt/mysql_nick_defailt_B.bak Enter password: 5> 总结 1、 备份用-B参数。增加use db,和create database的信息。 2、 用gzip对备份的数据压缩。
5.2备份多个数据库
备份多个数据库的情况呢？ #多个数据库名中间加空格 [root@localhost ~]# mysqldump -uroot -p -B nick_defailt oldsuo oldsuo_1|gzip>/opt/mul.sql.gz Enter password:
5.3备份单个及多个表
那如果备份单个和多个表，怎么办？ 1> 语法：mysqldump -u 用户名 -p 数据库名 表名 > 备份的文件名 [root@localhost ~]# mysqldump -uroot -p nick_defailt student >/opt/mysql_nick_defailt_student.bak Enter password: 2> 语法：mysqldump -u 用户名 -p 数据库名 表名1 表名2 > 备份的文件名 [root@localhost ~]# mysqldump -uroot -p nick_defailt student oldsuo >/opt/mysql_nick_defailt.bak Enter password:
5.4 mysqldump 的参数
mysqldump 的关键参数 1、 -B指定多个库，增加建库语句和use语句。 2、 --compact去掉注释，适合调试输出，生产不用。 3、 -A 备份所有库。 4、 -F刷新binlog日志。 5、 --master-data 增加binlog日志文件名及对应的位置点。 6、 -x，--lock-all-tables 7、 -l,--locktables 8、 -d 只备份表结构 9、 -t 只备份数据 10、 --single-transaction 适合innodb事务数据库备份。
5.6增量恢复
重要的来了，生产环境一般是增量备份与恢复；
所谓增量，就是在原数据的基础上继续添加数据，不必每次都重新添加，省时省力。
A：增量恢复必备条件：
1. 开启MySQL数据库log-bin参数记录binlog日志。 [root@localhost 3306]# grep log-bin /data/3306/my.cnf log-bin = /data/3306/mysql-bin 2. 存在数据库全备。
B：生产环境 mysqldump备份命令：
# 进行数据库全备，（生产环境还通过定时任务每日凌晨执行） mysqldump -uroot -pnick -S /data/3306/mysql.sock --default-character-set=gbk --single-transaction -F -B nick |gzip >/server/backup/mysql_$(date +%F).sql.gz # innodb引擎备份 mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -F --single-transaction -A -B |gzip >$DATA_FILE # myisam引擎备份 mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -F -A -B --lock-all-tables |gzip >$DATA_FILE
C：恢复：
# 通过防火墙禁止web等应用向主库写数据或者锁表。让主库暂时停止更新，然后再进行恢复。 # 误操作删除nick库！ 1. 检查全备及binlog日志 [root@localhost 3306]# cd /server/backup/ [root@localhost backup]# gzip -d mysql_2015-10-31.sql.gz [root@localhost backup]# vim mysql_2015-10-31.sql [root@localhost backup]# grep -i "change" mysql_2015-10-31.sql 2. 立即刷新并备份出binlog [root@localhost 3306]# mysqladmin -uroot -pnick -S /data/3306/mysql.sock flush-logs [root@localhost 3306]# cp /data/3306/mysql-bin.000030 /server/backup/ #误操作log-bin,倒数第二 [root@localhost backup]# mysqlbinlog -d nick mysql-bin.000030 >bin.sql #导出为.sql格式。 [root@localhost backup]# vim bin.sql 找到语句drop database nick删除！！！（误操作语句） 3. 恢复 [root@localhost backup]# mysql -uroot -pnick -S /data/3306/mysql.sock <mysql_2015-10-31.sql #恢复之前的数据库全备 [root@localhost backup]# mysql -uroot -pnick -S /data/3306/mysql.sock nick < bin.sql #恢复删除误操作语言的bin-log。