本文详细介绍了MySQL的主从复制配置，包括原理、脚本部署、读写分离、故障处理和级联复制。通过设置主从同步，实现数据实时备份和提升系统性能。同时讲解了在主库宕机时的应对策略和从库级联复制的步骤，确保数据安全和高可用性。
本章内容：
主从复制
简介原理
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: line26: 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<
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 status\G"|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 status\G"|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.00sec)
mysql>use sisi
Database changed
mysql>create table t(id int);
Query OK, 0 rows affected (0.31sec)
mysql> insert into t values(01);
Query OK,1 row affected (0.04sec)
mysql> select * fromt;+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.04sec)
[root@data-1-1 tools]#mysql -uroot -poldboy123 -S /data/3307/mysql.sock
mysql> select * fromsisi.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/3308chmod700 /data/3308/mysql
cd/application/mysql/scripts
./mysql_install_db --datadir=/data/3308/data --basedir=/application/mysql --user=mysql
chown-R mysql:mysql /data/3308egrep"server-id|log-bin" /data/3308/my.cnf/data/3308/mysql start
sleep5netstat-lnt|grep 3308mysqladmin-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 00.0.0.0:3307 0.0.0.0:* LISTEN 4332/mysqld
tcp 0 00.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 = 1expire_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
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 status\G; #查看从库3308状态
*************************** 1. row ***************************Slave_IO_State: Waitingformaster to send event
Master_Host:192.168.179.102Master_User: rep
Master_Port:3307Connect_Retry:60Master_Log_File: mysql-bin.000005Read_Master_Log_Pos:188Relay_Log_File: relay-bin.000007Relay_Log_Pos:334Relay_Master_Log_File: mysql-bin.000005Slave_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:188Relay_Log_Space:527Until_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.00sec)
ERROR:
No query specified
View Code
使用此种方法也能快速查看从库mysql的状态
[root@zsq data]#mysql -uroot -p'zsq3307' -S /data/3307/mysql.sock -e "show slave status\G;"|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.00sec)
mysql>drop database sisi1;
Query OK, 0 rows affected (0.00sec)
[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.01sec)
[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 frommysql.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 = 1log-bin = /data/3306/mysql-bin
expire_logs_days= 7
#________m-m m1 end________
……省略……
重启mysql
[root@zsq3306]#./mysql stop
Stoping MySQL...
[root@zsq3306]#./mysql start
Starting MySQL...
[root@zsq3306]#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 = 1log-bin = /data/3307/mysql-bin
expire_logs_days= 7
#________m-m m1 end________
……省略……
重启mysql
[root@zsq3307]#./mysql stop
Stoping MySQL...
[root@zsq3307]#./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.gz1)解压并将数据导入到3306
gzip-d 2016-06-10.sql
mysql-uroot -plx3306 -S /data/3306/mysql.sock < 2016-06-10.sql2)登录主数据库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 status\G;*************************** 1. row ***************************Slave_IO_State: Waitingformaster to send event
Master_Host:192.168.179.102Master_User: rep
Master_Port:3307Connect_Retry:60Master_Log_File: mysql-bin.000007Read_Master_Log_Pos:1986Relay_Log_File: relay-bin.000005Relay_Log_Pos:728Relay_Master_Log_File: mysql-bin.000007Slave_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:1986Relay_Log_Space:921Until_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.00sec)
ERROR:
No query specified4)在数据库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 * fromt1;+----+---------+
| 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 * fromt1;+----+---------+
| id | name |
+----+---------+
| 1 | oldgirl |
| 3 | oldboy |
+----+---------+mysql> insert into t1(name) values("lx");
mysql> insert into t1(name) values("swj");
mysql> select * fromt1;+----+---------+
| id | name |
+----+---------+
| 1 | oldgirl |
| 3 | oldboy |
| 4 | lx |
| 6 | swj |
+----+---------+
4 rows in set (0.00sec)
查看到数据库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.00sec)
mysql>use students;
Reading table informationfor completion of table andcolumn names
You can turn off this feature to get a quicker startup with-A
Database changed
mysql> select * fromt1;+----+---------+
| id | name |
+----+---------+
| 1 | oldgirl |
| 3 | oldboy |
| 4 | lx |
| 6 | swj |
+----+---------+
4 rows in set (0.00sec)8)3308的配置文件
[root@oldboy3308]#vim my.cnf
[client]
port= 3308socket= /data/3308/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user=mysql
port= 3308socket= /data/3308/mysql.sock
basedir= /application/mysql
datadir= /data/3308/data
open_files_limit= 1024back_log= 600max_connections= 800max_connect_errors= 3000table_cache= 614external-locking =FALSE
max_allowed_packet=8M
sort_buffer_size=1M
join_buffer_size=1M
thread_cache_size= 100thread_concurrency= 2query_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= 7key_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= 1skip-name-resolve
slave-skip-errors = 1032,1062replicate-ignore-db=mysql
server-id = 9innodb_additional_mem_pool_size=4M
innodb_buffer_pool_size=32M
innodb_data_file_path=ibdata1:128M:autoextend
innodb_file_io_threads= 4innodb_thread_concurrency= 8innodb_flush_log_at_trx_commit= 2innodb_log_buffer_size=2M
innodb_log_file_size=4M
innodb_log_files_in_group= 3innodb_max_dirty_pages_pct= 90innodb_lock_wait_timeout= 120innodb_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
View Code
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@oldboy3307]#netstat -ntpl|grep 3306
登陆从库，查看从库的线程更新状态
[root@oldboy3307]#mysql -uroot -plx3308 -S /data/3308/mysql.sock
mysql>show processlist\G*************************** 1. row ***************************Id:5User: system user
Host:
db: NULL
Command: Connect
Time:877066State: Waitingfor master to send event #IO线程
Info: NULL*************************** 2. row ***************************Id:6User: system user
Host:
db: NULL
Command: Connect
Time:3855State: Slave has read all relay log; waitingfor the slave I/O thread to update it #SQL线程 ，说明跟主库复制是最新的
Info: NULL*************************** 3. row ***************************Id:16User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist3 rows in set (0.00sec)4.2. 查看所有slave ，看哪个binlog大，哪个大哪个数据最新、#. 半同步的状态，就不用选了，直接就是它 (如果主库宕机，服务还能起来，把主库binlog补全)
[root@oldboy 3307]#cat /data/3307/data/master.info
18mysql-bin.000004
530740
192.168.179.102rep
lx1233306
600
01800.0000
[root@oldboy3307]#cat /data/3308/data/master.info
18mysql-bin.000008
107
192.168.179.102rep
lx1233307
600
01800.00004.3. 确保所有relay log全部更新完毕
mysql>stop slave io_thread;
mysql> show processlist\G #直到看到State: Slave has read all relay log;表示从库更新都执行完毕
mysql>quit4.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.00sec)4.5. 3308提升为主库
log-bin = /data/3308/mysql-bin
如果存在log-slave-updates read-only等一定注释它。
[root@oldboy3308]#/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:36data_bak.sq2-rw-r--r--. 1 root root 2109 10月 24 16:36data_bak.sql-rw-r--r--. 1 root root 1002 10月 27 11:55mysql_nick_defailt.bak-rw-r--r--. 1 root root 1002 10月 27 11:56mysql_nick_defailt.bak.gz-rw-r--r--. 1 root root 3201 10月 27 11:46mysql_nick_defailt_B.bak
drwxr-xr-x. 2 root root 4096 11月 22 2013rh-rw-r--r--. 1 root root 1396 10月 24 16:11student_bak.sql4>恢复nick_defailt数据库
[root@localhost~]#mysql -uroot -p nick_defailt
Enter password:#加-B恢复方法
[root@localhost ~]#mysql -uroot -p
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-tables7、 -l,--locktables8、 -d 只备份表结构9、 -t 只备份数据10、 --single-transaction 适合innodb事务数据库备份。
5.6增量恢复
重要的来了，生产环境一般是增量备份与恢复；
所谓增量，就是在原数据的基础上继续添加数据，不必每次都重新添加，省时省力。
A：增量恢复必备条件：
1. 开启MySQL数据库log-bin参数记录binlog日志。
[root@localhost3306]#grep log-bin /data/3306/my.cnf
log-bin = /data/3306/mysql-bin2. 存在数据库全备。
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@localhost3306]#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@localhost3306]#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
[root@localhost backup]#mysql -uroot -pnick -S /data/3306/mysql.sock nick < bin.sql#恢复删除误操作语言的bin-log。
扫一扫
434
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
