MySQL 5.5 master-slave synchronization settings Note sharing

Source: Internet
Author: User

First modify the my. cnf configuration of the Master ( ).

Add the following content to [mysqld:
Copy codeThe Code is as follows:
Log-bin = mysql-bin
Log-bin-index = mysql-bin.index
Server-id = 1
Sync_binlog = 1
Binlog_format = mixed

Specify the database to be synchronized and ignore the database that does not need to be synchronized.
Copy codeThe Code is as follows:
Binlog-do-db = testdb
Binlog-ignore-db = mysql
Binlog-ignore-db = performance_schema
Binlog-ignore-db = information_schema

In [mysqldump], modify the content
Copy codeThe Code is as follows: max_allowed_packet = 32 M

Note: Make sure that max_allowed_packet has a relatively large value, such as max_allowed_packet = 100 M.

Restart mysql:
Copy codeThe Code is as follows: service mysql restart

Create a copy user on the Master
Copy codeThe Code is as follows: create user funsion;
Grant replication slave on *. * TO funsion identified by 'jb51. net ';
# The password in the test environment is xybench.

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

Modify the Slave ( configuration and my. cnf

Add the following content in [mysqld ]:
Copy codeThe Code is as follows:
Server-id = 2
Log-bin = mysql-bin
Relay-log-index = slave-relay-bin.index
Relay-log = slave-relay-bin

Sync_master_info = 1
Sync_relay_log = 1
Sync_relay_log_info = 1

# The above three lines are only used in MySQL 5.5

Enter mysql-u root-p
Enter the mysql command line and enter
Copy codeThe Code is as follows:
Change master to MASTER_HOST = '10. 1.123.233 ', MASTER_USER = 'funsion', MASTER_PORT = 3306, MASTER_PASSWORD = 'ifunsion. com ';

Change master to MASTER_CONNECT_RETRY = 30;

Enter start slave;

+ ----------------------- Cannot be started if an error occurs ----------------- +

Enter the Master database
Enter show master status \ G
Record the currently used log-binfile and position (for example, the current log is a mysql-bin.000001, the location is 107)

Then go to the Slave Database
Enter stop slave;
Copy codeThe Code is as follows: change master to master_log_file = 'mysql-bin.000001 ', master_log_pos = 107;
Finally, enter start slave;

Finally, execute
Copy codeThe Code is as follows: yum-y install ntpdate
Synchronize time of several servers

+ ---------------------------- Some possible commands ------------------------- +

Execute lock table:
Copy codeThe Code is as follows: flush tables with read lock;
The purpose of this step is to avoid any new data in the master database during the Master/Slave creation process. Otherwise, the synchronization settings will be troublesome.

Unlock the master database:
Copy codeThe Code is as follows: unlock tables;

The reset master command deletes all binary log files and clears the binary log index files.
The reset slave command deletes all files used for Slave replication and starts again.

+ ----------------------- Other reference documents (not verified) ------------------------ +

When the database service cannot be stopped, create an image for the slave database:
Server 1: Master
Server 2: slave
Now, due to load problems, it is necessary to mount the slave server with the same number as the number 3 server.
However, database 1 cannot be stopped, database 2 cannot be stopped, and master-slave synchronization processes cannot be stopped (demanding ).
You can do this:
In the master:
Copy codeThe Code is as follows: mysqldump-uroot-pxxxx-h127.0.0.1 -- databases db1 db2 db3 -- master-data = 1> bak. SQL
Server 3 slave stop;
Then, import bak. SQL to No. 3 slave server.
Copy codeThe Code is as follows: slave start;
Server 3 automatically updates the node at the moment of export.
Because the -- master-data = 1 parameter is added with the change statement at the bottom after the SQL file is exported. If -- master-data = 0, it is not included.
It is very convenient, but it is only suitable for the case where the library is not too large. In this case, the exported database has a total of 6 GB.

Related Article

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: 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.