Detailed explanation of MySQL's master-slave replication, read-write separation, backup and recovery
- 巴扎黑Original
- 2017-04-09 10:21:093721browse
1. MySQL master-slave replication
1. Introduction
Why do we use master-slave replication?
Purpose of master-slave replication:
can do real-time backup of the database to ensure data integrity;
can separate reading and writing, the master server only writes, and the slave server only writes Read, which can improve overall performance.
Principle diagram: 
As can be seen from the above figure, synchronization is completed by synchronous reading and writing of log files.
#2. Change the configuration file
Operate the machine for two days, make sure the server-id is different, usually the main server The ID should be smaller than the secondary ID. Be sure to pay attention.
# 3306和3307分别代表2台机器 # 打开log-bin,并使server-id不一样 #vim /data/3306/my.cnf log-bin = /data/3306/mysql-bin server-id = 1 #vim /data/3307/my.cnf log-bin = /data/3307/mysql-bin server-id = 3 #检查 1、 [root@bogon ~]# egrep "log-bin|server-id" /data/3306/my.cnf log-bin = /data/3306/mysql-bin server-id = 1 [root@bogon ~]# egrep "log-bin|server-id" /data/3307/my.cnf log-bin = /data/3307/mysql-bin server-id = 3 2、 [root@localhost ~]# mysql -uroot -p -S /data/3306/mysql.sock -e "show variables like 'log_bin';" Enter password: +--------+--------+ | Variable_name | Value | +--------+--------+ | log_bin | ON | # ON 为开始开启成功 +--------+--------+
3. Establish an account for slave replication. rep
Usually create a dedicated account for master-slave replication. Do not Forget authorization.
# 主库授权，允许从库来连接我取日志 [root@localhost ~]# mysql -uroot -p -S /data/3306/mysql.sock Enter password: # 允许从库192.168.200网段连接，账号rep，密码nick。 mysql> grant replication slave on *.* to 'rep'@'192.168.200.%' identified by 'nick'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) # 检查创建的rep账号： mysql> select user,host from mysql.user; +-----+-------------+ | user | host | +-----+--------------+ | root | 127.0.0.1 | | rep | 192.168.200.% | | root | localhost | | root | localhost.localdomain | +-----+------------------+ 7 rows in set (0.00 sec)
4. Back up the main database and restore it to the slave database
Back up the existing data in the master database and restore it to the slave database , at this time the data of the two hosts are consistent.
If you have data in advance, you must not forget this.
#1) Lock the main library so that it only has read-only permissions.
mysql> flush table with read lock; Query OK, 0 rows affected (0.00 sec) #5.1、5.5锁表命令略有不同。 # 5.1锁表：flush tables with read lock; # 5.5锁表：flush table with read lock;
2) Remember to back up at this point.
mysql> show master status; +-------+------+--------+---------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------+------+--------+---------+ | mysql-bin.000013 | 410 | | | +-------+------+--------+---------+ 1 row in set (0.00 sec)
3) Clone the window and back up the data.
[root@bogon ~]# mysqldump -uroot -p -S /data/3306/mysql.sock -A -B --events --master-data=2|gzip >/opt/rep.sql.gz Enter password: 参数： -A：备份所有的 #看rep.sql.gz参数 vim /opt/rep.sql.gz -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=410;
4) Check the master status; whether the value is normal.
mysql> show master status; +------+------+---------+-------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------+-----+---------+--------+ | mysql-bin.000013 | 410 | | | +--------+----+---------+--------+ 1 row in set (0.00 sec)
5)Unlock the library
mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
6)Restore to the slave library
[root@bogon ~]# gunzip < /opt/rep.sql.gz | mysql -uroot -p -S /data/3307/mysql.sock Enter password:
5. Configure the slave library and take effect
Change the connection parameters of the slave library and the main library, and the configuration will take effect. The inspection was successful!
#1) Enter the slave library.
[root@bogon ~]# mysql -uroot -p -S /data/3307/mysql.sock Enter password:
2) Change the parameters used by the slave server to connect and communicate with the master server.
mysql> CHANGE MASTER TO MASTER_HOST='192.168.200.98', MASTER_PORT=3306, MASTER_USER='rep', MASTER_PASSWORD='nick', MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=410; Query OK, 0 rows affected (0.01 sec)
3) View the changed parameters.
[root@localhost ~]# cd /data/3307/data/ [root@localhost data]# cat master.info 18 mysql-bin.000013 410 192.168.200.98 REP nick 3306 60 0 0 1800.000 0
4) Effective!
mysql> start slave; Query OK, 0 rows affected (0.01 sec)
5) Check the following parameters, if they match, it is normal!
mysql> show slave status\G
Relay_Master_Log_File: mysql-bin.000013
Slave_IO_Running: Yes #取logo。
Slave_SQL_Running: Yes #读relay-bin、logo,写数据。
Seconds_Behind_Master: 0 #落后主库的秒数。6) View relay-bin.logo.
[root@localhost 3307]# cd /data/3307 [root@localhost 3307]# ll 总用量 48 drwxr-xr-x. 9 mysql mysql 4096 10月 29 18:52 data -rw-r--r--. 1 mysql mysql 1900 10月 29 11:45 my.cnf -rwx------. 1 root root 1307 10月 20 17:06 mysql -rw-rw----. 1 mysql mysql 6 10月 29 11:00 mysqld.pid -rw-r-----. 1 mysql mysql 15090 10月 29 18:49 mysql_nick3307.err srwxrwxrwx. 1 mysql mysql 0 10月 29 11:00 mysql.sock -rw-rw----. 1 mysql mysql 150 10月 29 18:49 relay-bin.000001 -rw-rw----. 1 mysql mysql 340 10月 29 18:52 relay-bin.000002 -rw-rw----. 1 mysql mysql 56 10月 29 18:49 relay-bin.index -rw-rw----. 1 mysql mysql 53 10月 29 18:52 relay-log.info
7) View relay-log.info.
[root@localhost 3307]# cat relay-log.info /data/3307/relay-bin.000002 340 mysql-bin.000013 497
8) View master.info.
[root@localhost 3307]# cat data/master.info 18 mysql-bin.000013 497 192.168.200.98 rep nick 3306 60 0 0 1800.000 0
6. Read-write separation
Read-write separation is common in production environments and is also a necessary skill.
Ignore MySQL master-slave replication authorization table synchronization and read-write separation.
[root@bogon 3306]# vim my.cnf #添加以下四行 replicate-ignore-db = mysql binlog-ignore-db = mysql binlog-ignore-db = performance_schema binlog-ignore-db = information_schema server-id = 1
1) Method to prevent data from being written to the slave library through the read-only parameter.
#修改配置文件。 vim /data/3307/my.cnf [mysqld] read-only #对用户授权事不能指定有super或all privileges权限。不然没效果。 #创建账户suoning，并刷新权限。 mysql> grant select,insert,update,delete on *.* to 'suoning'@'localhost' identified by '123'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) #用创建账户登录，并创建库 [root@bogon 3307]# mysql -usuoning -p123 -S /data/3307/mysql.sock mysql> create user kangkangkang@'192.%' identified by 'old123'; ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
2) Master-slave synchronization failure
A. Last_SQL_Errno: 1007 stop slave; set global sql_slave_skip_counter = 1; start slave; B.忽略 skip-name-resolve #忽略名字解析 slave-skip-errors = 1032,1062,1007 #忽略故障编号 server-id = 3
3) Open bin-log from the library
vim my.cnf log-bin = /data/3307/mysql-bin log-slave-updates #表示从库记录bin-log expire_logs_days = 7 #保留7天bin-log。
7. Master downtime
Let’s simulate it. If the host goes down, how can we quickly replace the slave? Minimize losses? Of course, dual-machine hot backup is also a good choice, which will be explained to you in the next section.
In an environment with one master and multiple slaves, if the master goes down, choose one slave to be the master and continue to synchronize with other slaves.
A.查看每个从库的master.info，看谁的更靠前，最新，更大，丢的数据最少。 [root@localhost 3307]# cat /data/3307/data/master.info mysql-bin.000015 326 B.确保所有relay log全部更新完毕。 在每个从库上执行stop slave io_thread;show processlist; 知道看到Has read all relay log;表示从库更新都执行完毕。 C.登陆mysql -uroot -p -S /data/3307/mysql.sock stop slave; reset master; quit D.进入到数据库目录，删除master.info relay-log.info cd /data/3307/data/ rm -f master.info relay-log.info E. 3307提升为主库 vim /data/3307/my.cnf 开启log-bin = /data/3307/mysql-bin 如存在log-slave-updates，read-only等一定要注释。 /data/3307/mysql restart F.其它从库操作 stop slave; change master to master_host ='192.168.200.98'; start slave; show slave status\G
8. Dual-primary
Use primary-primary premise: the primary key of the table increases automatically.
In the case of dual masters, the ID will be like this. The ID is written to the library M1: 1, 3, 5 and M2: 2, 4, 6 through the program.
[root@localhost 3307]# vim my.cnf [mysqld] auto_increment_increment = 2 auto_increment_offset = 2 [root@localhost 3307]# ./mysql restart [root@localhost 3306]# vim my.cnf [mysqld] auto_increment_increment = 2 auto_increment_offset = 1 log-bin = /data/3306/mysql-bin log-slave-updates [root@localhost 3306]# ./mysql restart [root@localhost 3306]# mysqldump -uroot -pnick -S /data/3307/mysql.sock -A -B --master-data=1 -x --events > /opt/3307bak.sql [root@localhost 3306]# mysql -uroot -pnick -S /data/3306/mysql.sock < /opt/3307bak.sql mysql> CHANGE MASTER TO MASTER_HOST='192.168.200.98', MASTER_PORT=3307, MASTER_USER='rep', MASTER_PASSWORD='nick'; mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status \G
2. MySQL backup and recovery
1. Back up a single database
The most basic backup of a single database.
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对备份的数据压缩。
2. Back up multiple databases
What about backing up multiple databases?
#多个数据库名中间加空格 [root@localhost ~]# mysqldump -uroot -p -B nick_defailt oldsuo oldsuo_1|gzip>/opt/mul.sql.gz Enter password:
3. Back up single and multiple tables
What should I do if I back up single and multiple tables?
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:
4. Parameters of mysqldump
Key parameters of mysqldump
-B指定多个库，增加建库语句和use语句。 --compact去掉注释，适合调试输出，生产不用。 -A 备份所有库。 -F刷新binlog日志。 --master-data 增加binlog日志文件名及对应的位置点。 -x，--lock-all-tables -l,--locktables -d 只备份表结构 -t 只备份数据 --single-transaction 适合innodb事务数据库备份。
5. Add Volume recovery
The important thing is coming. The production environment generally uses incremental backup and recovery; the so-called incremental means to continue to add data on the basis of the original data, without having to add it again every time. save time and energy.
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. Immediately refresh and back up the 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.Restore
[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。 # 搞定！！！
The above is the entire content of this article. I hope that the content of this article can bring some help to everyone's study or work.
The above is the detailed content of Detailed explanation of MySQL's master-slave replication, read-write separation, backup and recovery. For more information, please follow other related articles on the PHP Chinese website!