Background Introduction mysql5.5 previous version, MySQL master-slave replication is relatively simple
Mysql5.6:gtid,multi-thread replication
Master1 Enable binary Log log-bin = Master-binlog-bin-index = Master-bin.index
2 Select a unique server IDServer-id = [0~2^32]
3 Create a user with replication permissionsreplication Slave, copied from the nodereplication client, contact master, access to information
slave1 Enable binary Log relay-log = Relay-logrelay-log-index = Relay-log.index
2 Select a unique server ID that differs from the masterServer-id = [0~2^32]
3 connecting to the primary server to copy fileswhere do I start copying? 1) Master is new, slave copy from the beginning2) Master has been running for a while, performing a backup in master, logging the binary log file name and event location, in slave restore data, to 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 threadMaster launches 1 dump threads for each slaveMaster:dumpSlave:io_thread,sql_threadCan be started individuallymysql> start slave io_threadmysql> start slave sql_thread650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/83/A2/wKioL1d5GneiMxnOAACG0zBjF84685.jpg "border=" 0 " Name= "" "637" height= "209" >
5 semi-synchronous replication should specify a synchronization time-out and, once timed out, downgrade to asynchronous replication
MySQL master-slave asynchronous replication uses binary format to install MySQL and initialize, mtsql service script replication, my.cnf configuration file replication.
Export path, easy to use MySQL command# vim/etc/profile.d/mysql.sh
Export path= $PATH:/usr/local/mysql/bin
Master
1 Modifying a 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
Start mysql# service mysqld start
2 Authorized slave 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 Read-only
3 Viewing the master binary log file status, 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 ";
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/83/A3/wKiom1d5GnegxXrDAAArlD-a56g963.png "border=" 0 " Name= "" >
slave1 Modifying a configuration file
# VIM/ETC/MY.CNF[Mysqld]
DataDir =/data/mysql
innodb_file_per_table = 1
Relay-log = Relay-logrelay-log-index = Relay-log.indexserver-id = 10
Start mysql# service mysqld start
2 Configure slave sync settings and start slave copy 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 View slave status mysql> show slave status\gslave_io_state:waiting for Master to send event master_host:19 2.168.8.30 master_user:replicationuser master_port:3306 connect_retry: master_log_file:master-bin.000001 Read_master_log_pos:438relay_log_file:relay-log.000004 relay_log_pos:254 relay_master_log_file:master-bin.000001 Slave_io_running:YesSlave_sql_running:Yeslast_errno:0 last_error:skip_counter:0 Exec_master_log_pos:438
4 viewing slave in mastermysql> show slave hosts;
Additional settings and instructions1 writes to the database are not allowed on slave, so it is set to user read-only mode on slave, butThis setting is not valid for users with super privileges# VIM/ETC/MY.CNF[mysqld]read-only = On
Restart MySQL, which is effective # service mysqld restart restart Mysqld,replication threads are automatically restarted
After you modify the configuration,Or do not restart, directly modify the parameters in the database can also
See if read-only mode is in effect mysql> show global variables like ' read_only '; +---------------+-------+| variable_name | Value |+---------------+-------+| Read_Only | On |+---------------+-------+
2 to ensure that masterBin-lognot in buffer cache,synchronize to disk now to reduce the delay time of master-slave replication,in master settings[Mysqld]Sync-binlog = on
Restart MySQL, which is effective # service mysqld restart restart Mysqld,replication threads are automatically restarted
After you modify the configuration,Or do not restart, directly modify the parameters in the database can also
See if the sync now mode is in effect
Mysql> show global variables like ' sync_binlog '; +---------------+-------+| variable_name | Value |+---------------+-------+| Sync_binlog | 0 |+---------------+-------+
3 Restart Mysqld, the replication thread will restart automatically, what problems exist, how to prohibit? When Master performs some misoperation, due to delay reasons, the misoperation is not synchronized slave; At this time, slave off MySQL, backup data back to master, start slave MySQL, let io_thread skip just the wrong operation, and then start the replication function. If the slave starts, the master-slave copy starts immediately, and the error operation is synchronized just now. Do not allow it to start automatically with MySQL, preventing synchronous misoperation.
Connection master requires these two files Master.info,relay-log.info, temporary removal. Slave Unable to connect master
under the data file
Master.info recorded the login master and related information Relay-log.info recorded the Relay-log and Master-bin related information
4 related logs from the server are logged in the slave error log.
5 if master worked for a certain time, at this time the Master Lock table mysql> flush tables with read lock 5.1
5.2database export for 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 The operation and the new database are the same at this time, noteMaster_log_file,master_log_pos can
6 MySQL Replication filteringon the master# VIM/ETC/MY.CNF[Mysqld]BINLOG-DO-DB=DB1,DB2Binlog-ignore-db=db1,db2
Master Binlog-ignore-dbProblems brought about by Binary logs for partial databases are not logged and the binary log is incomplete. When the server crashes, only the data that records the binary log can be recovered, and records that are not logged will not be recoverable, so this option is not recommended.
Can be carried out on the slaveDatabase LevelFilter, can also be carried outtable LevelThe filter # VIM/ETC/MY.CNF[Mysqld]Database Levelreplicate-do-db=replicate-ignore-db=
Table Level replicate-do-table=replicate-ignore-table=
Use wildcard replicate-wild-do-table=mydb.tb% # at the table level to copy only all tables in mydb that begin with TB Replicate-wild-ignore-table=mydb.tb_# Copy only the table with the previous character in the mydb that starts with a TB
slavereplicate-ignore-dbproblems brought about byWith this option, the binary log for the specified slave database is not logged, but the trunk log is complete and therefore occupies slave bandwidth resources.
In conclusion, if the table must be filtered, it is recommended to do it on the slave. Semi-synchronous master-slave replication
1 MasterAdd Modulemysql> 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 time-out, unit ms| Rpl_semi_sync_master_trace_level | 32 | | Rpl_semi_sync_master_wait_no_slave | On |# Do I have to wait for slave to go online+------------------------------------+-------+
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 | |+---------------------------------+-------+
3 if the master-slave synchronization is turned on at this time, the settings will not take effect immediately, restart slave io_thread3.1 master Status View mysql> show global state 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 SlaveStatus ViewMysql> show global status like ' rpl% '; +----------------------------+-------------+| variable_name | Value |+----------------------------+-------------+|Rpl_semi_sync_slave_status|OFF|| Rpl_status | Auth_master |+----------------------------+-------------+
4 Only restart Io_thread can mysql> stop slave io_thread;
mysql> start slave io_thread;
4.1 MasterStatus ViewMysql> show global status like ' rpl% '; +--------------------------------------------+-------------+| variable_name | Value |+--------------------------------------------+-------------+| rpl_semi_sync_master_clients |1|+--------------------------------------------+-------------+
4.2 SlaveStatus ViewMysql> show global status like ' rpl% '; +----------------------------+-------------+| variable_name | Value |+----------------------------+-------------+| Rpl_semi_sync_slave_status | on|| Rpl_status | Auth_master |+----------------------------+-------------+
5 Testing When the half-sync expires (10000ms) Once, it is demoted to asynchronous replication. Stop at slaveIo_threadmysql> stop slave Io_thread;
When the master writes, it will get stuck 100000ms, then downgrade to asynchronous replication, recovery speed;
6 to make the parameters permanent, edit them 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 relatively fragile, if you need to restart Master, you need to stop slave replication, that is, stop slave. Monitor and monitor master-slave replication Tools Percona-toolkit(Mattkit-tools)https://www.percona.com/downloads/percona-toolkit/# Yum Localinstall-y percona-toolkit-2.2.18-1.noarch.rpm-- Nogpgcheck
After installation, there will be a lot of PT commands pt-slave-delay: Make slave slower than master pt-table-checksum: compare master-slave data with one-way encryption
Rhel uses the RPM library on the disc, unable to resolve the Percona dependent Yum Localinstall when the following prompts you could try using--skip-broken to working around the problem you co Uld try Running:package-cleanup--problems package-cleanup--dupes Rpm-va --nofiles--nodigestthe program Package-cleanup are found in the Yum-utils packagemysql SSL brief description when authorized, the SSL option is added to enforce the use of SSL, without this option, Unrestricted, even if SSL is enabled, you can mysql> grant replication slave on * * to ' replicationuser ' @ ' 192.168.8.31 ' identified when copying and not using SSL By ' Replicationuser 'Require SSL;
SSL requires content 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 | |+---------------+----------+
From for notes (Wiz)
List of attachments
MySQL master-slave replication--mysql-5.5 asynchronous, semi-synchronous configuration
