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

In the previous article, "Why do master-slave synchronous replication to MySQL" we explained the role of MySQL master-slave synchronization, the principle of master-slave synchronization and the shortcomings of master-slave synchronization. Next we introduce the next three nodes: two nodes are master-slave to each other, and one node is the slave of the first two nodes, which is used to implement the 3-node master-slave replication scheme of MySQL5.6.

The main steps are as follows:

1. Options required to configure synchronous replication on the MasterA side

two。 Create a user for synchronous replication on the MasterA master library and authorize

3.MasterA main library lock table

4. Record the binlog and pos location nodes of the MasterA main library

5. Export MasterA main library m_s_rep database

6. Options required to configure synchronous replication on the MasterB side

7. Create a database m_s_rep on MasterB and import the main library backup

8. Create a user for synchronous replication on MasterB and authorize

9. Record the binlog and pos location nodes of the MasterB slave library

10. Configure MasterA and MasterB to master and slave each other and enable slave library synchronization

11. Check the relay-log and master.info of MasterB

12.MasterA main library unlocked

13. Options required to configure synchronous replication on the Slave side

14. Create a database m_s_rep on the Slave side and import the main library backup

15. Configure the Salve side to synchronize MasterA and MasterB and enable synchronization

I. 1.IP address allocation instructions for environment configuration

Hostname IP address node

MYSQL01 10.62.83.201 MasterA

MYSQL02 10.62.83.202 MasterB

MYSQL03 10.62.83.203 Slave

two。 Protocol and Port

Node service port

MasterA mysqld 3306

MasterB mysqld 3306

Slave mysqld 3306

3. Package version

Software version number

OS CentOSrelease 6.7 (Final)

MySQL MySQL-5.6.27-1.el6.x86_64.rpm-bundle.tar

JDK java-1.8.0-openjdk-1.8.0.20-3.b26.el6.x86_64

II. Prepare before installing MySQL

Install the following MySQL5.6.27 dependency packages in each of the three nodes, and perform the following steps:

1. Dependent package installation # yum install-y openssl* openssl-devel zlib-devel gcc gcc-c++make autoconf readline-devel curl-devel expat-devel gettext-devel ruby

two。 Uninstall the default lower version of openjdk for OS

# rpm-qa | grep jdk # yum-y remove java-1.6.0 java-1.7.0

3. Install the java runtime environment openjdk-1.8.0

# yum-y install java-1.8.0

4. Configure the JDK environment variable $JAVA_HOME

# vim/etc/profile exportJAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.20-3.b26.el6.x86_64/bin/java#new add content

5. Configure DNS local domain name resolution

# vim/etc/hosts 10.62.83.201 MYSQL01 # new add content 10.62.83.202 MYSQL02 10.62.83.203 MYSQL03

6. Uninstall the older version of mysql-libs in OS

# cd / & & rpm-qa | grep mysql* # yum-y removemysql-libs-5.1.73-3.el6_5.x86_64

7. Firewall configuration

You can choose to turn off iptables or configure iptables rules.

III. Install the MySQL package

Install the following MySQL5.6.27 packages on each of the three nodes, and perform the following steps:

1. Extract the tar package # cd / usr/local/data # tar xfMySQL-5.6.27-1.el6.x86_64.rpm-bundle.tar

two。 Install the RPM package

# yum-y installMySQL-client-5.6.27-1.el6.x86_64.rpm MySQL-server-5.6.27-1.el6.x8664.rpm IV. Master-slave relationship configuration preparation

The password for the initial login of MySQL5.6.27 is a string of salt passwords. To facilitate login to MySQL, perform the following configuration on each of the three nodes:

1. Log in to MySQL configuration

Start the mysql service:

# service mysql start Starting MySQL. SUCCESS!

View the mysql initial key file for each node:

# cat/root/.mysql_secret # The random passwordset for the root user at Thu Nov 511 local time 12 13 2015 (MzmPQ1uPJ0Sgyki4)

Log in to mysql:

# mysql-u root-p # copy and paste or manually enter the key

After logging in, set the new password for mysql login:

Mysql > SET PASSWORDFOR 'root'@'localhost' = PASSWORD (' mysql'); mysql > flushprivileges; mysql > exit

two。 Configuration allows Client remote login Server mysql > use mysql; Database changed mysql > selecthost,user,password from user; # only local client remote login is allowed by default

Mysql > update user set host='%' where user='root' andhost='localhost';mysql > flush privileges;mysql > exit

Note:

Another way to change the user password is:

Mysql > update user setpassword=password ('PASSWORD') where user='USER'

3. Create a database m_s_rep on MasterA

Create a database m_s_rep on MasterA to prepare for synchronous replication of data between master and slave nodes.

Create databases and tables on masterA:

Mysql > createdatabase m_s_rep

five。 Master-slave synchronous replication configuration

First of all, the Mastera side and the MasterB side are configured as the master and slave, and finally the Slave side is realized as the slave.

Options required to configure synchronous replication on the MasterA side

1. Copy template file

# cp / usr/share/mysql/my-default.cnf / etc/my.cnf # vim / etc/my.cnf

The configuration / etc/my.cnf file for the 2.MasterA node is as follows

[mysqld] user=mysqlserver-id=1 # represents the mysql server ID, and the ID must be unique among the host. The default is 1, and the ID can be customized, but it must be numeric. Log-bin=mysql-bin # means to enable mysql binary logging, which must be enabled, or the mysql master will never take effect. Log-bin-index=mysql-bin.indexrelay-log=mysql-relayrelay-log-index=mysql-relay.indexexpire-logs-days=10max-binlog-size=100M # represents the maximum size of each binlog file, and when the file size is equal to 100m, a new log file is automatically generated. Note: a record is not written in 2 log files, so sometimes the log file exceeds this size. Log-slave-updates=1binlog-do-db=m_s_rep # indicates the name of the database that needs to be synchronized. If it is multiple databases, you can write another line in this format. Replicate-do-db= m_s_rep binlog-ignore-db=mysql # indicates the name of a database that does not need to be synchronized. If it is multiple databases, you can write another line in this format. Replicate-ignore-db=mysqlsync-binlog=1auto-increment-increment=2auto-increment-offset=1# Remove leading # and set to the amount of RAM for the mostimportant data# cache in MySQL. Start at 70% of total RAM for dedicatedserver, else 10%. # innodb_buffer_pool_size = 128M## Remove leading # to turn on a very important data integrityoption: logging# changes to the binary log between backups.# log_bin## Remove leading # to set options mainly useful for reportingservers.# The server defaults are faster for transactions and fastSELECTs.# Adjust sizes as needed Experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2Mdatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assortedsecurity riskssymbolic-links=0 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES # default mysql modelower_case_table_names=1explicit_defaults_for_timestamp=trueskip-character-set-client-handshakecollation-server=utf8_general_cicharacter-set-server=utf8

Note:

If binlog-do-db and binlog-ignore-db are not added, the default is to replicate the entire mysql database synchronously.

Configure a variety of server operational characteristics by setting sql mode:

Specify the tolerance of mysql for accepting input data

Set compatibility with other database systems

Control query processing

-hires or disables behaviors related to sql compliance.

Override the "empty" default mode of sql

-empty mode does not restrict employment or comply with sexual behavior

The default sql mode is no_engine_substitution

The default profile adds STRICT_TRANS_TABLES

Create a user for synchronous replication on the MasterA master library and authorize

1. Create 2 new accounts on masterA to synchronize data between masterB and slave

Mysql > GRANTREPLICATION SLAVE ON *. * TO 'rep201'@'10.62.83.%' IDENTIFIED BY' rep201'; mysql > GRANTREPLICATION SLAVE ON *. * TO 'rep203'@'10.62.83.%' IDENTIFIED BY' rep202'; mysql > FLUSH PRIVILEGES

Mysql > select user,repl_slave_priv from user; # users who view authorized replication slave

two。 After authorization, we need to test whether rep201 and rep203 users can connect to master in MasterB and Slave. As follows:

# mysql-h 10.62.83.201-umysql01-p

MasterA main library lock table

First lock the table of the master database MasterA to prevent data from being written again, resulting in inconsistency between the master and slave databases.

Mysql > FLUSH TABLES WITH READ LOCK; # main library master lock table; mysql > insert into TABLE values (6); # test whether the table is locked

Note: lock table command window do not exit, lock table is invalid after exit.

Record the binlog of the MasterA main library and the pos location node mysql > show master status;mysql > show master status\ G

Note: at this time, the database is still locked, do not exit this window, exit the lock table will be invalid

Export MasterA main library m_s_rep database # mysqldump-u root-p-- databases m_s_rep > / tmp/m_s_rep.sql Enter password: # scp / tmp/m_s_rep.sql root@10.62.83.202:/tmp

Options required to configure synchronous replication on the MasterB side

1. Copy template file

# cp / usr/share/mysql/my-default.cnf / etc/my.cnf # vim / etc/my.cnf

The 2.MasterB node configuration / etc/my.cnf file is as follows:

[mysqld] user=mysqlserver-id=2log-bin=mysql-binlog-bin-index=mysql-bin.indexrelay-log=mysql-relayrelay-log-index=mysql-relay.indexexpire-logs-days=10max-binlog-size=100Mlog-slave-updatesskip-slave-startslave-skip-errors=allbinlog-do-db=m_s_repreplicate-do-db= m_s_repbinlog-ignore-db=mysqlreplicate-ignore-db=mysqlsync-binlog=1auto-increment-increment=2auto-increment-offset=2 # # Remove leading # and set to the amount of RAM for the mostimportant data# cache in MySQL. Start at 70% of total RAM for dedicatedserver, else 10%. # innodb_buffer_pool_size = 128M## Remove leading # to turn on a very important data integrityoption: logging# changes to the binary log between backups.# log_bin## Remove leading # to set options mainly useful for reportingservers.# The server defaults are faster for transactions and fastSELECTs.# Adjust sizes as needed Experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2Mdatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assortedsecurity riskssymbolic-links=0 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESlower_case_table_names=1explicit_defaults_for_timestamp=trueskip-character-set-client-handshakecollation-server=utf8_general_cicharacter-set-server=utf8 create a database m_s_rep on MasterB and import the main library backup

1. Replicating database backups on MasterA

# scp / tmp/m_s_rep.sql root@10.62.83.203:/tmp/

two。 Create database m_s_rep on MasterB and import database backup

Create a user for synchronous replication on MasterB and authorize

1. Create an authorized account in masterB to enable synchronization

Mysql > GRANTREPLICATION SLAVE ON *. * TO 'rep202'@'10.62.83.%' IDENTIFIED BY' rep202'

Mysql > select user,repl_slave_priv from user; # users who view authorized replication slave

two。 After authorization, we need to test whether rep202 users can connect to MasterB in MasterA and Slave.

Record the binlog and pos location nodes of the MasterB slave library

1. Record the binlog file name of the main library masterB and the pos location node:

Mysql > show master status;mysql > show master status\ G

Configure MasterA and MasterB to master and slave each other and enable slave library synchronization

1. Configure MasterA slave library on MasterB 10.62.83.202 and enable slave library synchronization

Mysql > CHANGE MASTER TO MASTER_HOST='10.62.83.201',MASTER_USER='rep201', MASTER_PASSWORD='rep201', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=1544, MASTER_CONNECT_RETRY=10;mysql > start slave

two。 Configure MasterB slave library on MasterA 10.62.83.201 and enable slave library synchronization

Mysql > CHANGE MASTER TO MASTER_HOST='10.62.83.202', MASTER_USER='rep202',MASTER_PASSWORD='rep202', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=1691, MASTER_CONNECT_RETRY=10;mysql > start slave

Note:

Let's see if synchronization is mainly about looking at the Slave_IO_Running and Slave_SQL_Running options. If synchronized normally, both options must be YES.

If Slave_IO_Running is NO, it may be that the slave library is not connected to the master library.

If Slave_SQL_Running is NO, it is likely that the data of the slave database is inconsistent with that of the master database.

From the figure above, we can see that both Slave_IO_Running and Slave_SQL_Running are currently YES. It shows that master-slave synchronization is normal now.

And from the figure above, we can also see the binlog file name at the beginning of synchronization between the slave library slave and the master library master and the pos location node at the beginning of synchronization.

Check the relay-log and master.info of MasterB

1. View the relay-log of MasterB

Now let's take a look at the relay-log and master.info information of MasterB. Let's first look at the relay-log information, as follows:

# mysqlbinlog mysql-relay-bin.000002 | more

From this figure, we can see in the relay-log log that the binlog file name of the main library MasterA and the pos location node during synchronous replication are synchronized from the library MasterB.

two。 Now let's take a look at MasterB's master.info, as follows:

# cat master.info | more

You can see that this file holds the information about synchronizing the master library MasterA from the library MasterB: IP, user, password, binlog file name, pos location node, synchronization cycle.

3. View the process status of MasterB

Mysql > SHOW PROCESSLIST\ G

Note:

Check the status, where the first, second and third processes are the three threads from which the server starts; the fourth BinlogDump process is provided as the master to the slave thread that produces binlog

4. View the process status of MasterA

Note:

Check the status, where the third, fourth and fifth processes are the three threads from which the server starts; the first and second Binlog Dump processes are provided as the master to the slave thread that produces binlog

MasterA master library unlocks mysql > UNLOCK TABLES

Options required to configure synchronous replication on the Slave side

1. Copy template file

# cp / usr/share/mysql/my-default.cnf / etc/my.cnf # vim / etc/my.cnf

The 2.Slave-side configuration / etc/my.cnf file is as follows:

[mysqld] user=mysql server-id=3log-bin=mysql-binlog-bin-index=mysql-bin.indexrelay-log=mysql-relayrelay-log-index=mysql-relay.indexexpire-logs-days=10max-binlog-size=100Mlog-slave-updatesskip-slave-startslave-skip-errors=allbinlog-do-db= Remove leading repreplicateMurray _ Dobb = m_s_repbinlog-ignore-db=mysqlreplicate-ignore-db=mysqlsync-binlog=1 # # house # and set to the amount of RAM for the mostimportant data# cache in MySQL. Start at 70% of total RAM for dedicatedserver, else 10%. # innodb_buffer_pool_size = 128M## Remove leading # to turn on a very important data integrityoption: logging# changes to the binary log between backups.# log_bin## Remove leading # to set options mainly useful for reportingservers.# The server defaults are faster for transactions and fastSELECTs.# Adjust sizes as needed Experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2Mdatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assortedsecurity riskssymbolic-links=0 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESlower_case_table_names=1explicit_defaults_for_timestamp=trueskip-character-set-client-handshakecollation-server=utf8_general_cicharacter-set-server=utf8

Note: restart the mysql service after modifying the file / etc/my.cnf

Create a database m_s_rep on the Slave side and import the main library backup

1. Create a database m_s_rep on the server side

two。 Replicating database backups on MasterA

# scp / tmp/m_s_rep.sql root@10.62.83.203:/tmp/

3. Import master database data backup

Note: make sure the data is consistent before importing the data

Configure the Salve side to synchronize MasterA and MasterB and enable synchronization

1. Configure the slave library of MasterA on Slave 10.62.83.203 and enable slave synchronization

Mysql > CHANGE MASTER TO MASTER_HOST='10.62.83.201',MASTER_USER='rep203', MASTER_PASSWORD='root', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1544,MASTER_CONNECT_RETRY=10;mysql > start slave

Next, "3-node master-slave synchronous replication scheme test of MySQL"

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.