MySQL main master sync server build

MySQL main master sync server build




Install the database first here is the mysql5.6.15 version of the

[Email protected] ~]# RPM-UVH mysql-*.rpm
Preparing ... ########################################### [100%]
1:mysql-devel ########################################### [14%]
2:mysql-client ########################################### [29%]
3:mysql-test ########################################### [43%]
4:mysql-embedded ########################################### [57%]
5:mysql-shared-compat ########################################### [71%]
6:mysql-shared ########################################### [86%]
7:mysql-server ########################################### [100%]

Start the database

[[Email protected] ~]# service MySQL start
Starting MySQL. Determine

View the database default password (version 5.6 will automatically generate a default password password file is/root/.mysql_secret)

[email protected] ~]# cat. Mysql_secret
# The random password set for the root user at Tue Sep 9 21:37:58 (local time): juooyw9v

Connect to the database

mysql-uroot-pjuooyw9v

Modify the database default password (do not modify the class action cannot be changed)

Set Password=password ("123456");










Master-Slave configuration section

(Primary server)

Modify MySQL "My. CNF profile" (Master server)

server_id=10

Log_bin Open Binlog Log
Log_slave_updates=1 Allow cascade replication (used in primary master configuration)
Auto_increment_increment=1 (Specify the starting value for the self-growing field)
auto_increment_offset=2 (Specifies the step size of the self-growing field from growth)
Skip-name-resolve (do not do domain name resolution)

The two options of Auto-increment-incrment and Auto-increment-offset are set to prevent collisions between 2 servers that produce critical fields. If they use different cheap, such as one according to 1,3,5,7 increase and the other one according to that 2,4,6,8 increase. auto-incrment-increment=2 automatically incremented field each step is 2,auto-increment-offset=1 the initial value of the Automatically incremented field is 1 (this configuration cannot be used if the business environment has to be contiguous)

Authorization to slave the replication permissions required by the user in the master-slave replication (permission to read the Binlog log file)

Mysql-u root-p

Grant Replication Slave on * * to [e-mail protected] "192.168.213.%" identified by "[email protected]"; (There are multiple slave servers so authorized the entire network segment, of course, can also be authorized to each)




Primary server information

[Email protected] mysql]# pwd

/var/lib/mysql

[[email protected] mysql]# ls

AUTO.CNF Localhost-bin.index Mysql.sock

Ibdata1 master1-bin.000001 Performance_schema

IB_LOGFILE0 Master1-bin.index Rpm_upgrade_history

Ib_logfile1 Master1.pid Rpm_upgrade_marker-last

localhost-bin.000001 MySQL Test

[Email protected] mysql]#







(from server)



server_id=11

Log_bin

Log_slave_updates=1

auto_increment_increment=2

auto_increment_offset=2

Skip-name-resolve




Configuring Master-Slave synchronization

Mysql-uroot-p




Change Master to

Master_host= "192.168.213.10",

Master_user= "Slaveuser",

master_password= "[Email protected]",

Master_log_file= "master1-bin.000001",

master_log_pos=340; Show master status on master server, log related information can be queried

You can choose to sync from the beginning by simply master_log_file= the "first bin_log log file" Master_log_pos=1; Can

Start slave; Start the synchronization server




show slave status; View from the database state if the IO and SQL modules are normal, you can perform a master-slave test.

Slave_io_running:yes

Slave_sql_running:yes




Main master structure start (change master to Slave)

Viewing from server information

Grant Replication Slave on * * to [e-mail protected] "192.168.213.%" identified by "[email protected]"; Authorized




Mysql> Show master status;

+--------------------+----------+--------------+------------------+-------------------+

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

+--------------------+----------+--------------+------------------+-------------------+

|      master2-bin.000002 |              639 |                  |                   | |

+--------------------+----------+--------------+------------------+-------------------+

1 row in Set (0.00 sec)




To the master configuration becomes from the

Change Master to


Master_host= "192.168.213.11",

Master_user= "Slaveuser",

master_password= "[Email protected]",

Master_log_file= "master2-bin.000002",

master_log_pos=639;




Start slave; Start the synchronization server




show slave status; View from the database state if the IO and SQL modules are normal, you can perform a master-slave test.

Slave_io_running:yes

Slave_sql_running:yes

This article is from the "Zhengerniu" blog, make sure to keep this source http://liufu1103.blog.51cto.com/9120722/1656837

MySQL main master sync server build