MySQL Master master configuration

MySQL Master master configuration

MySQL dual master (Master) architecture scheme ideas

1. Two MySQL can read and write, the main preparation, by default only one (Mstera) responsible for data writing, another (Masterb) standby

2.masterA is the main library of Masterb, Masterb is the main library of Mastera, they are mainly from

3. High availability between two main libraries, can use keepalived and other programs (using VIP to provide services)

4. Master-Slave synchronization of all services from server to Masterb (dual master multiple Slave)

5. Highly available policies are recommended, mastera or masterb do not preempt VIP (non-preemption mode) after downtime recovery

This will ensure that the main library is highly available to a certain extent, and after a main library is down, it can be switched to another main library in a very short time (minimizing the impact of the main library outage on the business), reducing the pressure on the master-slave synchronization to the main library;

But there are also a few deficiencies:

1.masterB may always be idle (it can be used when from the library, responsible for part of the query); 2. After the main library to provide services from the library to wait for the Masterb to synchronize the data before going to masterb up to synchronize data, which may cause a certain degree of synchronization delay;

Deployment environment:

Master (mastera_mysql): 192.168.87.100 CentOS 7

From (masterb_mysql): 192.168.87.101 CentOS 7

vip:192.168.87.102

1, master server modified MySQL configuration file, configuration file location:/etc/my.cnf, in the file [MySQL] add content

[Email protected] etc]# VI/ETC/MY.CNF
#主主备份配置
Server-id=1 #任意自然数n, just make sure that the two MySQL hosts are not duplicated.
Log-bin=mysql-bin #开启二进制日志
Relay-log=mysql-relay-bin

auto_increment_increment=2 #步进值auto_imcrement. Normally there are N main MySQL to fill n
Replicate-wild-ignore-table=mysql.% #replicate-wild-ignore-table representatives do not participate in backup
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

2, slave server modify MySQL configuration file, configuration file location:/etc/my.cnf, in the file [MySQL] add content

[Email protected] etc]# VI/ETC/MY.CNF
#主主备份配置
server-id=2 #任意自然数n, just make sure that the two MySQL hosts are not duplicated.
Log-bin=mysql-bin #开启二进制日志
Relay-log=mysql-relay-bin

auto_increment_increment=2 #步进值auto_imcrement. Normally there are N main MySQL to fill n
Replicate-wild-ignore-table=mysql.% #replicate-wild-ignore-table representatives do not participate in backup
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

Restart the MYSQ service when you are finished configuring

Note: Do not use BINLOG-DB or binlog-ignore-db on the main library or use REPLICTE-DO-DB or replicate-ignore-db from the library, as this may cause cross-library updates to fail. It is recommended to use the replicate_wild_do_table and replicate-wild-ignore-table two options from the library to troubleshoot responsible process filtering.

3. Manually synchronize the database

You need to synchronize the Mastera and Masterb databases before performing primary master interop, first backing up the database on Mastera

Mysql>flush TABLES with READ LOCK;

Do not exit the terminal, otherwise the lock fails, re-open the previous terminal, directly packaging compressed database files or using the Mysqldump tool to export data

[email protected] etc] #cd/var/lib/

[[Email protected] etc]# tar zcvf mysql.tar.gz mysql

[email protected] etc] #scp mysql.tar.gz [email protected]:/var/lib/#选择yes and enter the password

4. Create a replication user and authorize

Create a user in 192.168.87.100 that can log in from 192.168.87.101 and give permission

MariaDB [sampdb]> unlock tables; #解除锁表
Query OK, 0 rows Affected (0.00 sec)

MariaDB [Sampdb]>grant REPLICATION SLAVE on * * to ' repl_nuser ' @ ' 192.168.1.101 ' identified by ' repl_passwd '; #创建用户并设置权限

MariaDB [Sampdb]l>flush privileges; #刷新权限

5. View the database binary log name and location on 192.168.87.100

Mysql>show Master status;

6. Execute in 192.168.87.101

Mysql>change MASTER to

->master_host= ' 192.168.87.100 ',

->master_user= ' Repl_user ',

->master_password= ' repl_passwd ',

->master_log_file= ' mysql-bin.000002 ',

->master_log_pos=418;

You can then start the slave server on the Masterb,

Execution: Mysql>start slave;

To view the running status from the server:

show slave status \g;

Complete 192.168.87.101 to 192.168.87.100 Backup

Implement 192.168.87.100 Backup of the 192.168.87.101

1, in 192.168.87.101 (masterb_mysql) to create 192.168.87.100 (mastera_mysql) access to the account

MariaDB [sampdb]> Grant Replication Slave on * * to ' repl_user ' @ ' 192.168.87.100 ' identified by ' Repl_password ';

MariaDB [sampdb]> FLUSH privileges; #刷新权限
Query OK, 0 rows Affected (0.00 sec)

2. View Master Status

3. Set Masterb_mysql as the primary server in Mastera_mysql MySQL library

MariaDB [(none)]> change Master to

Master_host= ' 192.168.87.101 ',

Master_user= ' Repl_zhengwei ',

Master_password= ' Sky!zheng ',

Master_log_file= ' mysql-bin.000003 ',

master_log_pos=1331;
Query OK, 0 rows affected (0.06 sec)

4. Then execute

MariaDB [(None)]> start slave;
Query OK, 0 rows Affected (0.00 sec)

5 Viewing status

MySQL Master master configuration