MySQL master-slave replication configuration process

Main library configuration

1. Configure mysql

vim /etc/my.cn
# Add the following content to the file server-id=1 # Ensure that the server id is unique log-bin = /var/lib/mysql/mysql-bin.log
binlog-do-db = db1 
binlog-do-db = db2

Among them, db1 and db2 are the libraries planned for master-slave replication. If there are multiple ones, just write multiple lines. After configuration, restart the database: service mysqld restart restart

2. Add replication user

Through phpmyadmin, add a new user, and grant REPLICATION SLAVE permissions. Or log in to mysql as root user and grant permissions:

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'password'

Where repl is the username and password is the password of that user.

3. Dump data

If you can ensure that no new data is generated, there is no need to lock the database. If this cannot be guaranteed, you need to lock the database for each dump data:

use db1;
FLUSH TABLES WITH READ LOCK;

Then dump the data:

mysqldump -uroot -p db1 > db1.sql

If there are multiple databases, repeat the above process multiple times.

4. Check the binary file location

Log in to MySQL as root and check the master status:

show master status;

This command will produce output similar to the following format:

+-----------------+-----------+----------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+-----------+----------------+------------------+
| mysql-bin.00002 | 445 | db1,db2 | |
+-----------------+-----------+----------------+------------------+
1 row in set (0.00 sec)

5. Unlock

If the number of exports is first and the database is read locked, you need to release it:

use db1;
UNLOCK TABLES;

At this point, the main database configuration is complete. In particular, remember the file and position values ​​in show master status output.

Configuration from the library

1. Create a new database

First, create a database with the same name on the slave library.

2. Configure the slave database

vim /etc/my.cnf
# Add the following content to my.cnf:
server-id = 2 # Ensure that the id is unique relay-log = /var/lib/mysql/mysql-relay-bin.log
replicate-wild-do-table = db1.%
replicate-wild-do-table = db2.%

If you synchronize multiple databases, replicate-wild-do-table needs to write multiple lines. After configuration, restart the database.

3. Import data

Import the data dumped from the master server into the slave server:

mysql -uroot -p db1 < db1.sql

4. Configure master-slave synchronization

Log in to the MySQL server and configure the synchronized master server information:

> change master to master_host='xxx.xxx.xx.xx',
> master_user='repl',
> master_password='password',
> master_log_file='mysql-bin.00000x',
> master_log_pos=xxxx;

After configuration, start master-slave synchronization: start slave . Then check the status through the command: show slave status . If successful, both slave_io_running and slave_sql_running should be yes.

test

Operations such as adding new records or adding tables on the master database can almost be reflected in the slave database in real time.

The above is the details of the MySQL master-slave replication configuration process. For more information about MySQL master-slave replication configuration, please pay attention to other related articles on 123WORDPRESS.COM!