Shulou(Shulou.com)06/01 Report--

The following is mainly about how to configure mysql master-slave replication, mysql-5.5 async and semi-synchronization. I hope these contents can bring you practical use. This is also the main purpose of my article on how to configure mysql master-slave replication, mysql-5.5 async and semi-synchronization. All right, don't talk too much nonsense, let's just read the following.

Master1 enable binary log log-bin = master-binlog-bin-index = master-bin.index2 Select a unique server idserver-id = [0 ~ 2 ^ 32] 3 to create a user replication slave with replication permissions, replicate slave node replication client, contact master Permission to obtain information slave1 enable binary log relay-log = relay-logrelay-log-index = relay-log.index2 choose a unique server id, which is different from the master server-id = [0 ~ 2 ^ 32] 3 connect to the primary CVM to copy files from where to start? 1) master is new, slave replicates from scratch 2) master has been running for some time, performing a backup in master, recording the binary log file name and event location, restoring data in slave, which binary file is connected to which location? Mysql > change master to master_host=, master_port=, master_log_file=, master_log_pos=, master_user=, master_password=; mysql > start slave;4 mysql replication thread master will start one slave thread for each master:dumpslave:IO_thread,SQL_thread. Mysql > start slave IO_threadmysql > start slave SQL_thread can be started separately.

5 in semi-synchronous replication, the synchronous timeout should be specified. Once the timeout occurs, it will be degraded to asynchronous replication.

Mysql master-slave asynchronous replication installs mysql in binary format, initializes, copies mtsql service scripts, and copies my.cnf configuration files.

Export PATH to facilitate the use of the mysql command # vim / etc/profile.d/mysql.sh

Export PATH=$PATH:/usr/local/mysql/binmaster

1 modify the configuration file # vim / etc/my.cnf

[mysqld] datadir = / data/mysql

Innodb_file_per_table = 1log-bin=master-binlog-bin-index=master-bin.indexserver-id = 1 starts mysql# service mysqld start2 authorizes slave to copy mysql > grant replication slave on *. * to 'replicationuser'@'192.168.8.31' identified by' replicationuser'

Mysql > flush privileges

Mysql > flush tables with read lock; # Lock table to read-only 3 to view the status of master binary log files. You need to use mysql > show master status on slave +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +- -+-+ | master-bin.000001 | 355 | +-+ mysql > show binlog events in "master-bin.000001"

Slave1 modify configuration file

# vim / etc/my.cnf [mysqld]

Datadir = / data/mysql

Innodb_file_per_table = 1relay-log = relay-logrelay-log-index = relay-log.indexserver-id = 10 launch mysql# service mysqld start2 configuration slave synchronization settings and start slave replication mysql > change master to master_host='192.168.8.30',master_user='replicationuser',master_password='replicationuser',master_log_file='master-bin.000001',master_log_pos=355

Mysql > start salve 3 check the status of slave mysql > show slave status\ GSlave_IO_State: Waiting for master to send event Master_Host: 192.168.8.30 Master_User: replicationuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 438 Relay_Log_File: relay-log.000004 Relay_Log_Pos: 254 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 4384 View slavemysql > show slave hosts in master Other settings and instructions 1 slave do not allow database write operations, so it is set to user read-only mode on slave, but this setting is not valid for users with super privileges # vim / etc/ my.cnf [mysqld] read-only = on

Restart mysql, effective # service mysqld restart restart mysqld, and the replication thread will restart automatically

After modifying the configuration, or without restarting, you can directly modify the parameters in the database to check whether the read-only mode is in effect mysql > show global variables like 'read_only' +-+-+ | Variable_name | Value | +-+-+ | read_only | ON | +-+-+ 2 to ensure that master bin-log is not in the buffer cache, synchronize it to disk immediately to reduce the delay time of master-slave replication Set [mysqld] sync-binlog = on to restart mysql in master, that is, restart mysqld with # service mysqld restart, and the replication thread will restart automatically.

After modifying the configuration, or without restarting, you can directly modify the parameters in the database to see whether the immediate synchronization mode is effective.

Mysql > show global variables like 'sync_binlog';+-+-+ | Variable_name | Value | +-+-+ | sync_binlog | 0 | +-+-+ 3 restart mysqld and the replication thread will restart automatically. What are the problems and how to prohibit them? When master performs some misoperations, the misoperation is not synchronized due to delay. At this time, slave; closes mysql in slave, restores backup data to master, starts slave's mysql, and lets IO_thread skip the misoperation, and then start the replication feature. If the master-slave replication starts immediately after slave starts, it will also synchronize the misoperation just now. Do not let it start automatically with mysql to prevent synchronization misoperation.

These two files, master.info,relay-log.info, are required to connect to master, which can be temporarily removed. Slave cannot connect to master

Under the data file

Master.info records login master and related information relay-log.info records relevant information about relay-log and master-bin 4 the relevant log from the CVM will be recorded in the error log of slave. 5 if master has been working for a certain period of time, the matters needing attention for master and slave are 5.1 master lock table mysql > flush tables with read lock

5.2 Database Export of master mysql # mysqldump mydb > mydb.sql5.3 slave create a database with the same name and import the database # mysqldump mydb

< mydb.sql 5.4 此时操作和全新的数据库是相同的，注意master_log_file,master_log_pos即可6 mysql复制过滤在master上# vim /etc/my.cnf[mysqld]binlog-do-db=db1,db2binlog-ignore-db=db1,db2 master binlog-ignore-db带来的问题 不记录部分数据库的二进制日志，二进制日志不完整。当云服务器崩溃时，只能恢复记录了二进制日志的数据，未记录的将不能恢复，因此不建议使用此选项。在slave上可以进行数据库级别的过滤，也可以进行表级别的过滤# vim /etc/my.cnf[mysqld]数据库级别replicate-do-db=replicate-ignore-db= 表级别replicate-do-table=replicate-ignore-table=在表级别使用通配replicate-wild-do-table=mydb.tb% # 仅复制mydb中以tb开头的所有表replicate-wild-ignore-table=mydb.tb_ # 仅复制mydb中以tb开头的、后面跟上一个字符的表slave replicate-ignore-db带来的问题 使用此选项，虽不记录指定slave数据库的二进制日志，但是中继日志是完整的，因此会占有slave的带宽资源。 综上，如果必须对表进行过滤，建议在slave上进行。半同步主从复制1 master 添加模块mysql>

Install plugin rpl_semi_sync_master soname 'semisync_master.so'

Mysql > set global rpl_semi_sync_master_enabled = 1

Mysql > show variables like 'rpl%' +-- +-+ | Variable_name | Value | +-+-+ | rpl_recovery_rank | 0 | | rpl_semi_sync_master_enabled | ON | | rpl_semi _ sync_master_timeout | 10000 | # Asynchronous replication timeout Unit ms | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | # whether you must wait for slave to launch +-- +-- + 2 slave add module mysql > install plugin rpl_semi_sync_slave soname 'semisync_slave.so'

Mysql > set global rpl_semi_sync_slave_enabled = 1

Mysql > show variables like 'rpl%' +-- +-+ | Variable_name | Value | +-+-+ | rpl_recovery_rank | 0 | | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_trace _ level | 32 | +-- +-+ 3 if master-slave synchronization is enabled at this time The setting will not take effect immediately. You need to restart slave io_thread3.1 master status to check mysql > show global status like 'rpl%'. +-+-+ | Variable_name | Value | +-+- -+ | Rpl_semi_sync_master_clients | 0 | Rpl_semi_sync_master_net_avg_wait_time | 0 | Rpl_semi_sync_master_net_wait_time | 0 | Rpl_semi_sync_master_net_waits | 0 | Rpl_semi_sync_master_no_times | 0 | Rpl_semi_sync_master_no_tx | 0 | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl _ semi_sync_master_tx_avg_wait_time | 0 | Rpl_semi_sync_master_tx_wait_time | 0 | Rpl_semi_sync_master_tx_waits | 0 | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | Rpl_semi_sync_master_wait_sessions | 0 | Rpl_semi_sync_master_yes_tx | 0 | Rpl_status | AUTH_MASTER | +- -+-+ 3.2 slave status View mysql > show global status like 'rpl%' +-- +-+ | Variable_name | Value | +-+-+ | Rpl_semi_sync_slave_status | OFF | | Rpl_status | AUTH_MASTER | +- -+-+ 4 only restart io_thread to mysql > stop slave io_thread

Mysql > start slave io_thread

4.1 master status View mysql > show global status like 'rpl%' +-+-+ | Variable_name | Value | +-+- -+ | Rpl_semi_sync_master_clients | 1 | +-+-+

4.2 slave status View mysql > show global status like 'rpl%' +-- +-+ | Variable_name | Value | +-+-+ | Rpl_semi_sync_slave_status | ON | | Rpl_status | AUTH_MASTER | +- -+-+ 5 test after 10000ms (half synchronous timeout) Will be degraded to asynchronous replication. Stop io_threadmysql > stop slave io_thread on slave

When master performs write operations, it will jam 100000ms, and then degrade to asynchronous replication and restore speed. 6 in order to make the parameters permanent, edit in the my.cnf of Master and Slave: # On Master [mysqld] rpl_semi_sync_master_enabled=1 rpl_semi_sync_master_timeout=1000 # 1 second # On Slave [mysqld] rpl_semi_sync_slave_enabled=1 7 mysql master-slave mechanism is fragile. To restart master, you need to stop slave replication, that is, stop slave. Monitoring and monitoring master-slave replication tool percona-toolkit (mattkit-tools) https://www.percona.com/downloads/percona-toolkit/# yum localinstall-y percona-toolkit-2.2.18-1.noarch.rpm-- after nogpgcheck installation, there will be a lot of pt commands pt-slave-delay: make slave slower than master pt-table-checksum: compare whether the master-slave data is consistent by one-way encryption rhel uses the rpm library on the CD When percona's dependence on yum localinstall cannot be solved, the following prompt appears: You could try using-- skip-broken to work around the problem You could try running: package-cleanup-- problems package-cleanup-- dupes rpm-Va-- nofiles-- nodigestThe program package-cleanup is found in the yum-utils packagemysql ssl briefly states that when authorization is granted, add the ssl option to force the use of ssl Without this option, there is no restriction. Even if the ssl feature is enabled, you can mysql > grant replication slave on *. * to 'replicationuser'@'192.168.8.31' identified by' replicationuser' require ssl with and without ssl when replicating.

Content required by ssl mysql > show global variables like'% ssl%' +-+-+ | Variable_name | Value | +-+-+ | have_openssl | DISABLED | | have_ssl | DISABLED | | ssl_ca | | ssl_capath | | ssl_cert | | ssl_cipher | | ssl_key | | +-+-+ |

For the above about how to configure mysql master-slave replication, mysql-5.5 asynchronous and semi-synchronous, we do not find it very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.