Master-slave synchronization configuration and read-write separation of MySQL database

The benefits of using MySQL master-slave replication are:

1. The master-slave server architecture is adopted to improve stability. If the primary server fails, we can use the slave server to provide services.

2. Processing user requests separately on the master and slave servers can improve data processing efficiency.

3. Copy the data on the master server to the slave server to protect the data from accidental loss.

Environment Description:

New enterprises need to build a MySQL database with a master-slave replication architecture.

Master server (mysql-master): IP address: 192.168.48.128, mysql installed, no user data.

Slave server (mysql-slave): IP address: 192.168.48.130, mysql installed, no user data.

Both the master and slave servers can provide services normally.

Configuring the master server

1. Edit the database configuration file my.cnf or my.ini (windows), usually in the /etc/ directory.

Add the following code below [mysqld]:

log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog-do-db=wordpress
binlog_ignore_db=mysql

illustrate:

server-id=1 //The 1 in server-id=1 can be defined arbitrarily as long as it is unique.

binlog-do-db=wordpress // means backing up only wordpress.

binlog_ignore_db=mysql //Indicates ignoring backup of mysql.

Without binlog-do-db and binlog_ignore_db, all databases will be backed up.

2. Then restart MySQL:# service mysqld restart restart

3. Log in to MySQL, add a backup account in MySQL, and authorize it to the slave server.

[root@localhost~]#mysql -u root –p 123456 Log in to mysql
mysql>grant replication slave on*.* to 'backup'@'192.168.48.130' identifiedby 'backup';

Create a backup user and authorize 192.168.48.130 to use the account.

4. Query the status of the master database and write down the values ​​of FILE and Position, which will be used when configuring the slave server later.

mysql>show masterstatus; Please write down the displayed information, which will be used when configuring the slave server.

+——————+———-+————–+——————

+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|

+——————+———-+————–+——————

+|mysql-bin.000001|253|dbispconfig|mysql|

+——————+———-+————–+——————+

1rowinset(0.00sec)

On the slave server:

1) Make sure that /etc/my.cnf contains the parameters log-bin=mysql-bin and server-id=1, and change server-id=1 to server-id=10. After modification, it looks like this:

[mysqld]

log-bin=mysql-bin //Start binary file server-id=10 //Server ID

2) Restart the MySQL service.

[root@localhost~]#mysqladmin-p123456shutdown

[root@localhost~]#mysqld_safe--user=mysql&

3) Log in to mysql and execute the following statement

[root@localhost~]#mysql-uroot–p123456

mysql>changemastertomaster_host='192.168.48.128',master_user='backup',master_password='backup',master_log_file='mysql-bin.000003',master_log_pos=401;

4) Start slave synchronization.

mysql>start slave;

5) Check the master-slave synchronization. If you see that both Slave_IO_Running and Slave_SQL_Running are Yes, the master-slave replication connection is normal.
mysql> show slave status\G

Verify whether the configuration is normal and whether the MySQL master-slave can replicate normally.

Create a new database on the main database and write a table and some data in the database.

[root@localhost~]#mysql -u root –p 123456

mysql>create database mysqltest;

mysql>use mysqltest;

mysql>create table user(idint(5),namechar(10));

mysql>insert into user values(00001,'zhangsan');

Verify from the database whether the data is copied normally.

[root@localhost~]#mysql -u root –p 123456

mysql>show databases;

mysql>select * from mysqltest.user;