mysql video tutorial Column introduces master-slave replication

##Related free learning recommendations: mysql video tutorial
Database replication plays a very important role in improving system high availability and high performance. This article summarizes the relevant knowledge involved in mysql master-slave replication. If you happen to be working in this area, I hope you can learn more about it. You helped.
1 Main library configuration
1.1 my.cnf configuration:
Configure the following basic configuration in the main library configuration file my.cnf:
1 2 3 4 5 | log-bin = mysql-bin
log-bin-index = mysql-bin.index
server-id = 1
binlog-format = mixed
#sync_binlog=1
|
Copy after login
Default Copy all databases. If you need to specify a database, please refer to Section 7 (Copy Filtering).
1 2 3 | 比如说要指定db1和db2两个数据库进行主从复制:
binlog- do -db = db1
binlog- do -db = db2
|
Copy after login
1.2 Add a copy account:
Add a copy account and set permissions:
1 2 | mysql> grant replication slave, replicatin client on \*.\* to repl@ '172.16.226.192' identified by 'repl123456' ;
mysql> flush privileges;
|
Copy after login
2 Standby database configuration
In the standby database configuration file my.cnf Make the following basic configuration in:
1 2 3 4 5 6 7 8 9 10 | relay-log = slave-relay-bin
relay-log-index = slave-relay-bin.index
server-id = 2
#read_only = 1
log_slave_updates = 1
skip_slave_start
即使开启了建议的选项,备库仍然可能在崩溃后被中断,因为master.info和中级日志文件都不是崩溃安全的,所以建议开启一下选项:
sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1
|
Copy after login
You can also filter the database or table to be synchronized, please refer to the section on replication filtering.
3 Database remote backup
Database remote backup can choose mysqldump (logical backup) for hot backup, but it will be slower when the amount of data is large. Xtrabackup (physical backup) can also perform hot backup on mysql The database performs hot backup (innobackupex-1.5.1 is used here). Xtrabackup can realize online backup of databases such as innoDB, which is fast and does not affect normal reading and writing. Back up the entire database here.
3.1 Create a backup account
Create user backup on the main server (use minimum permissions) for database backup.
1 2 | mysql> grant reload, lock tables, replication client on \*.\* to backup@ '%' identified by 'backup123' ;
mysql> flush privileges;
|
Copy after login
3.2 Full database backup
Both steps of full backup and recovery preparation are completed on the main database server.
1 2 3 4 | innobackupex-1.5.1 --defaults-file=/etc/mysql/my.cnf --user=backup --password=backup123 /mysqlbackup
--defaults-file:选择默认的配置文件
--user和--password:分别为进行备份的用户名和密码
/mysqlbackup:目标目录
|
Copy after login
3.3 Recovery preparation
Generally, after the backup is completed, the data cannot be used for recovery operations because the backed up data may contain transactions that have not yet been committed or have been committed but not yet Synchronized to transactions in data files. Therefore, the data files are still dealing with an inconsistent state at this time. The main function of "preparation" is to make the data files in a consistent state by rolling back uncommitted transactions and synchronizing committed transactions to the data files.
The --apply-log option of the innobakupex command can be used to implement the above functions. For example, the following command:
1 2 3 4 5 6 | innobackupex-1.5.1 --apply-log --user=backup --password=backup123 /mysqlbackup/2017-01-11_21-20-57
如果执行正确,其最后输出的几行信息通常如下:
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
120407 9:01:36 InnoDB: Starting shutdown...
120407 9:01:40 InnoDB: Shutdown completed; log sequence number 92036620
120407 09:01:40 innobackupex: completed OK!
|
Copy after login
In the process of implementing "preparation", innobackupex can usually also use the --use-memory option to specify the size of the memory it can use. The default is usually 100M. If enough memory is available, you can allocate more memory to the prepare process to increase its completion speed.
3.4 Data copy
Copy the database prepared on the master server to the slave server. (Of course, you can also package and then copy)
1 | scp -r /mysqlbackup/ copyer@192.168.1.192:/data/
|
Copy after login
3.5 Data recovery
Before data recovery, first close the slave server mysql service and obtain it from the xtrabackup_binlog_info file in the backup folder The binary log file currently in use, and the location where the binary log events up to this point are backed up. If the datadir directory is not empty, you also need to clear the datadir directory. The --copy-back option of the innobackupex command is used to perform a recovery operation. It performs the recovery process by copying all data-related files to the datadir directory of the mysql server. innobackupex obtains relevant information about the datadir directory through backup-my.cnf (you can also specify the my.cnf directory through --defaults-file, and make sure the datadir path is empty)
1 2 3 4 5 6 7 | innobackupex-1.5.1 -- copy -back /mysqlbackup
如果执行正确,其输出信息的最后几行通常如下:
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/backup/2012-04-07_08-17-03'
innobackupex: back to original InnoDB log directory '/mydata/data'
innobackupex: Finished copying back files.
120407 09:36:10 innobackupex: completed OK!
|
Copy after login
Please make sure the last part of the above information is A line "innobackupex: completed OK!" appears.
After the data is restored to the datadir directory, you need to ensure that the owners and groups of all data files are the correct users, such as mysql. Otherwise, you need to modify the owners of the data files before starting mysqld. and group. For example:
1 | chown -R mysql:mysql / var /lib/mysql/
|
Copy after login
4 Master-slave connection
4.1 Open the slave database
If opening mysql fails, you can find the reason for the failure by checking the error log.
4.2 Establish a master-slave connection
The slave library connects to the master library through the replication account: (slave must be in stop state for the following connection to take effect)
1 2 | mysql> change master to master_host= '192.168.1.208' ,master_user= 'repl' ,
master_password= 'repl123456' ,master_log_file= 'mysql-bin.000001' (备份时得到的活动日志),master_log_pos=0(备份时得到的活动日志中事件的位置);
|
Copy after login
Note : If the master cannot be connected here during the master-slave connection, one possible reason is that the local machine is bound in the my.cnf configuration file, that is, bind-address = 127.0.0.1. What we need to do Just comment it out, otherwise external machines will not be able to access it.
Open slave:
Check the slave status, you can find that the IO thread and SQL thread are already in the open state, and there are many variables that represent the slave connection status (these variables can also be used to set Master-slave monitoring), we will not introduce them one by one here.
1 2 3 4 5 | mysql> show slave status;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
|
Copy after login
4.3 Common monitoring commands
1 2 3 4 5 6 7 8 | mysql> show processlist\G;
mysql> show master/slave status\G;
mysql> flush logs;
mysql> show binlog events in '指定二进制日志文件名称' from (从指定位置开始显示) limit (需要显示的事件数量)\G;
mysql> show binary logs;
mysql> reset master;
mysql> reset slave;
mysql> show slave hosts;
|
Copy after login

5 The slave library delay is large
If you find that the slave library delay is large, you need to find The reason for the large delay. The parameter innodb_flush_log_at_trx_commit has a greater impact on the writing efficiency of mysql and has three values:
1 2 3 | 0:每隔一秒,把事务日志缓存区的数据写到日志文件中,以及把日志文件的数据刷新到磁盘上;
1:每个事务提交时候,把事务日志从缓存区写到日志文件中,并且刷新日志文件的数据到磁盘上;
2:每事务提交的时候,把事务日志数据从缓存区写到日志文件中;每隔一秒,刷新一次日志文件,但不一定刷新到磁盘上,而是取决于操作系统的调度;
|
Copy after login
取1时的IO耗费最大,虽然一致性和完整性方面效果最好,但是写入效率最低,而这也是导致从库延迟较大的原因(如果服务器配置较高或许会好些)。取0时mysql写入性能很好,但如果 mysqld 进程崩溃,通常会导致最后 1s 的日志丢失 。取2时的写入性能也很好,每次事务提交会写入日志文件,但并不会立即刷写到磁盘,日志文件会每秒刷写一次到磁盘。这时如果 mysqld 进程崩溃,由于日志已经写入到系统缓存,所以并不会丢失数据;在操作系统崩溃的情况下,通常会导致最后 1s 的日志丢失。
6 混合模式复制
正常情况下使用使用基于语句的复制,而对不安全的语句则切换到基于行的复制。主要有以下几种情况:
- 该语句调用了:
- UUID函数
- 用户自定义函数
- CURRENT_USER或USER函数
- LOAD_FILE函数
- 一个语句同时更新了两个或者两个以上含有AUTO_INCREMENT列的表
- 语句使用了服务器变量
- 存储引擎不允许使用基于语句的复制,例如,mysql cluster引擎
7 复制过滤
有时候我们不需要对数据库中所有的库进行复制,或者不想对指定库中的某些表进行复制操作,那么我们就需要对复制进行一定的过滤配置,以达到更合理的复制效果。
1. 基于master
1 2 | **binlog- do -db=mysql**:主库只是将指定库(mysql)发生的变化记录到二进制日志中。
**binlog-ignore-db=mysql**:主库取消将指定库(mysql)发生的变化记录到二进制日志中。
|
Copy after login
2. 基于slave
针对数据库进行的过滤:
1 2 3 4 5 | **replicate- do -db=mysql**:从库只是将指定库(mysql)发生的变化进行重现。
**replicate-ignore-db=mysql**:从库取消将指定库(mysql)发生的变化进行重现。
针对表进行的过滤:
**replicate-wild_do-table=mysql.learn**:从库只是将指定库(mysql)中指定表(learn)发生的变化进行重现。
**replicate-wild_ignore-table=mysql.learn**:从库取消将指定库(mysql)中指定表(learn)发生的变化进行重现。
|
Copy after login
以上复制过滤方式乍一看没有问题,其实还是有需要注意的地方。因为这些过滤方式的效果与复制方式有关系。如果是基于语句的复制,binlog-do-db、binlog-ignore-db、replicate-do-db、replicate-ignore-db与跨库(如use库内和use外)有关系,这一点需要注意。
8 日志清理
暴力清理:(没有主从复制的情况下)
1 2 | 1、重启mysql服务器即可关闭bin日志的记录
2、通过reset master命令进行清理
|
Copy after login
条件清理
如果存在主从复制关系,则应当使用purge的方式来清理bin日志,语法如下:
1 2 | purge {master|binary} logs to 'log_name'
purge {master|binary} logs before 'date'
|
Copy after login
用户删除列于在指定的日志或日期之前的日志索引中的所有二进制日志,同时这些日志也会从日志索引文件的清单中删除。
1 2 3 4 | eg.
purge master logs to 'mysql-bin.000005' ;
purge master logs before '2014-08-30 00:00:00' ;
purge master logs before date_sub(now(),Interval 3 day);清除三天前的日志
|
Copy after login
定时清理
参数:expire_logs_days
说明:二进制日志自动删除/过期的天数。默认值为'0',即没有过期的
示例:expire_logs_days = 5,代表日志的有效时间为5天
什么时候会删除过期日志?
1 | 每次进行log flush 的时候会自动删除过期的日志
|
Copy after login
什么时候会触发log flush?
1 2 3 | 1、重启
2、binlog文件的大小达到了最大限制
3、手动执行 flush logs命令
|
Copy after login
写在最后
本文只是结合自己的学习以及实践过程进行了相关总结,如有不妥之处望您批评指正。推荐大家学习《高可用MYSQL》、《高性能MYSQL》两本书,最重要的还是实践实践再实践,欢迎交流,共同进步。
想了解更多编程学习,敬请关注php培训栏目!
The above is the detailed content of mysql master-slave replication. For more information, please follow other related articles on the PHP Chinese website!