Mysql master-slave synchronization configuration method

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

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

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

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

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

@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

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;

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

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:

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;

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!