Master slave Synchronous Configuration and Read write Separation of MySQL Database

2021-10-11 19:49:56
OfStack

The benefits of using mysql master-slave replication are:

1. By adopting the architecture of master-slave server, the stability is improved. If the master server fails, we can use the slave server to provide services.

2. Processing users' requests separately on master and slave servers can improve data processing efficiency.

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

Environment description:

New enterprises should build mysql database with master-slave replication architecture.

Primary 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.

Master and slave servers can provide services normally.

Configure Master Server (master)

1. Edit the database configuration file my. cnf or my. ini (windows), generally 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

Description:

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

binlog-do-db=wordpress//means that only wordpress is backed up.

binlog_ignore_db=mysql//indicates that backup mysql is ignored.

If binlog-do-db and binlog_ignore_db are not added, the entire database is backed up.

2, then restart MySQL: # service mysqld restart

3. Log in to mysql, add an backup account in mysql, and authorize it to the slave server.

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

Create an backup user and license it to 192.168. 48.130.

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 note the information displayed, which will be used by the configuration slave server.

+--------------------------------------------

+--------------------------------------------

+------------------------------------------------

1rowinset(0.00sec)

To operate on the slave server:

1), ensure that the parameters log-bin=mysql-bin and server-id=1 are included in /etc/my. cnf, and modify server-id=1 to server-id=10. After modification, it is as follows:

[mysqld]


log-bin=mysql-bin // Start 2 Binary document 
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-urootp123456

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 master-slave synchronization, and if you see that Slave_IO_Running and Slave_SQL_Running are both Yes, the master-slave replication connection is normal.
mysql > show slave status\G

Verify that the configuration is normal and mysql master and slave can replicate normally.

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

[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');

Under Verify 1 from database, whether the data is copied to normally.

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

mysql > show databases;

mysql > select * from mysqltest.user;