Mysql master-slave synchronization configuration method

1. Install mysql on two Ubuntu machines
1. Check whether mysql is installed in the system

##This is already installed

If not installed, execute the previous command

==================================
MySQL Some simple management:

启动MySQL服务： sudo start mysql
停止MySQL服务： sudo stop mysql
修改 MySQL 的管理员密码： sudo mysqladmin -u root password newpassword

2. Configuration on the Master main server (103.251.237.42)

1. Edit my.cnf (command to find the file location: find / -name my.cnf )

Comment out bind-address in [mysqld] = 127.0.0.1 otherwise mysql cannot be remote

#server-id = 1 in 1 can be defined by yourself, but it needs to be kept unique and is the unique identification of the server

log_bin 启动MySQL二进制日志
binlog_do_db 指定记录二进制日志的数据库
binlog_ignore_db 指定不记录二进制日志的数据库。

##Comment out binlog_do_db and binlog_ignore_db, which means backing up all databasesAfter doing this After that, restart the database

2. Log in to the main server mysql and create the account and permissions used by the slave server;

@After that, the IP can access the main server, and the value here is set Create a new masterbackup user with the password masterbackup from the server IP

and grant replication slave permission

You can see that the user masterbackup has been added

3. View The status of the master database

Record mysql-bin.000007 and 276, write the following command for later use;

change master to master_host='103.251.237.42',master_port=3306,master_user ='masterbackup',master_password='masterbackup',master_log_file='mysql-bin.000007',master_log_pos=276;

3. Slave slave server configuration (103.251.237.45)
1. Edit my. cnf (command find file location: find / -name my.cnf)


In [mysqld]

relay-log = slave- relay-bin

relay-log-index = slave-relay-bin.index

It is not clear what this does. Add these two items.

Restart the mysql service

Log in to mysql and stop the synchronization command

Execute the command prepared above; Log in to the Slave server , connect to the Master server:

Restart data synchronization;

View Slave information; as shown in the figure If both sentences are yes, the status is normal

4. Test results from the master and slave servers

Create a database on the master server

Check the database just created on the slave server

You can find that the master-slave server configuration is complete.

================================================ ==================================================

Because the above is a mysql master-slave configuration for two new machines; there is no need to import the previous data from the master server to the slave server; then if the master server already has some data, you need to add the import from the master server Operation of data to slave server;

After configuring the master server to grant permissions to the slave server;

1. Execute the following command to lock the database to prevent data from being written;

This command is a global read lock. After executing the command, all tables in all libraries will be locked as read-only. It is generally used for database online backup. At this time, the write operation of the database will be blocked and the read operation can proceed smoothly;

2. Exit the mysql command line, export the database to the root file and name it all.sql;

3. Use the scp command to transfer the database file all.sql to From the server;

4. Connect to the database again and enter the mysql command to view the master status and record the values ​​of file and position;

5 .Unlock the data table;

Before configuring the slave server, import the data first;

1. Import the database of the master server;

2. The subsequent configuration is the same as the previous slave server configuration;

The above is the detailed content of Mysql master-slave synchronization configuration method. For more information, please follow other related articles on the PHP Chinese website!