Mysql master-master-slave experiment
Implementation structure:
Prerequisite, this is a new installation of mysql server, already running mysql needs to make spare parts, and restore to another machine.
Master A:192.168.168.128
Master B:192.168.168.129
Slave 192.168.168.130
Install mysql on three machines
[root@yaho ~] # yum install-y mysql mysql-server mysql-dev
Modify the configuration files for master An and master B and slave.
[root@yaho ~] # vim / etc/my.cnf
Master A: add (modify) in [mysqld]
Server-id = 128,
Log-bin = mysql-bin
Sync-binlog = 1
Auto_increment_offset = 1 # starting value
Auto_increment_increment = 2 # step
Slave-skip-errors=1007,1008 # skips the unable to create database error and the unable to delete database error. If all mysql databases are the same, do not add
# slave-skip-errors=all # skip all errors. In fact, this parameter is quite dangerous and can easily cause data inconsistencies among several mysql.
Log-slave-updates # writes records of replication operations from master to the local bin-log log to achieve full synchronization of slave (backup)
Master B: add (modify) in [mysqld]
Server-id = 129
Log-bin = mysql-bin
Sync-binlog = 1
Auto_increment_offset = 2 # starting value
Auto_increment_increment = 2 # step
Slave-skip-errors=1007,1008 # skips the unable to create database error and the unable to delete database error. If all mysql databases are the same, do not add
# slave-skip-errors=all # skip all errors. In fact, this parameter is quite dangerous and can easily cause data inconsistencies among several mysql.
Log-slave-updates # writes records of replication operations from master to the local bin-log log to achieve full synchronization of slave (backup)
Slave: add (modify) in [mysqld]
Server-id = 130
Slave-skip-errors=1007,1008 # skips the unable to create database error and the unable to delete database error. If all mysql databases are the same, do not add
# slave-skip-errors=all # skip all errors. In fact, this parameter is quite dangerous and can easily cause data inconsistencies among several mysql.
Log-slave-updates # writes records of replication operations from master to the local bin-log log to achieve full synchronization of slave (backup)
3. Start the mysql of three master respectively, establish synchronous users, and authorize IP to allow remote access.
[root@yaho ~] # service mysqld start
Master A:
Mysql > GRANT REPLICATION SLAVE ON *. * TO 'repl'@'192.168.2168.129' identified by' 123456authorization; # authorize master B
Master B:
Mysql > GRANT REPLICATION SLAVE ON *. * TO 'repl'@'192.168.168.128' identified by' 123456license; # authorize master A
Mysql > GRANT REPLICATION SLAVE ON *. * TO 'repl'@'192.168.168.130' identified by' 123456authorization; # authorize slave
4. View maste status information for master An and master:
Mysql > show master status
+-+ +
| | File | Position |
+-+ +
| | mysql-bin.000001 | 106 | |
+-+ +
5. Connect master to realize master-master-slave
Master A:
Mysql > change master to master_host='192.168.168.129',master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=106
Master B:
Mysql > change master to master_host='192.168.168.128',master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=106
Slave:
Mysql > change master to master_host='192.168.168.129',master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=106
6. Start the slave function on each of the three machines:
Mysql > start slave
Mysql > show slave status\ G
If both of the following are YES, it means success.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Test-slightly:
All you have to do is create a database in any master, and then show it, and if the other two mysql have this database, it means success.
Note: if mysqld does not start, you can comment on the line log-slave-updates first, and then remove the comment when the master slave is established.
