MySQL main master sync server build

Source: Internet
Author: User
Tags local time

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.