MySQL Master-slave configuration

I. Introduction to the Environment

Operating system:CentOS 6.5

Database:mysql 5.1.73

Second, the configuration steps

PS:linux kernel operating system download, install mysql is not the focus of this article, so do not introduce more.

The configuration file path involved in the article is based on the above environment, the operating system and MySQL version inconsistency may lead to different paths.

Primary (Master) configuration:

1. In the /var/lib/mysql/ directory to establish the ***.log file, the file will be used in the third step, mainly to record the master-slave data synchronization operation log file, Stand-alone database service can choose to open or not to open, but the master-slave configuration environment must be turned on, because the server needs to have a record master-slave synchronization of the place, this log file is.

2. use chmod-r 777/etc/my.cnf to modify The permissions of the MySQL configuration file.

3. Open the my.cnf file and add it below [mysqld]

Server-id=1 ( randomly named, but guaranteed to be unique throughout the master-slave configuration )

Log-bin=/var/lib/mysql/***.log.

Note: After modifying the my.cnf file, be sure to modify the file's permissions to read-only, or MySQL will give a warning, this warning will cause you to modify the configuration does not work, I have been in the pit for a long time. You can use show variables like ' log_bin '; if value to be on The instructions are configured correctly.

4. start the MySQL service using the command service mysqld start and use mysql-u [user]-p to login MySQL .

5. Use the command grant replication slave on * * to ' user ' @ '% ' identified by ' password '; establish the account number and password from the server.

6. Use the command show master status; View status and record File and the Port The value in the field, which is required when configuring the slave server.

Note: At this point, the primary server configuration process has ended, try not to do any operation on the primary server, otherwise it will cause prot Change, if you must operate, after the operation is finished using the 6 The step command again to view the status information.

From the (slave) server configuration:

1. Refer to Steps 1,2,3 in the master server configuration ,server_id is required ,log-bin dispensable. After the configuration is complete , start the service using the command service mysqld start .

2. Log in to the database server.

3. Use the command change master to

master_host= Primary server address ,

Master_user=user,

Master_password=password,

master_log_file= Configuring the file value in step 6 of the master server

master_log_pos= Configuring the Port value in step 6 of the master server ;

4. Use the command start slave; start from the server.

Note: If startup fails, use the command show variables like ' server_id '; View value whether to 0 , if the 0 Check the section 1 configuration of the step.

At this point, the MySQL master-slave construction is completed, on the basis of which we can read and write access to the master-slave configuration to achieve the separation.

Third, testing

Create a database table in the master server and add records, switch to the server to query the table, you can see mysql since the implementation of master-slave data synchronization.

MySQL Master-slave configuration