Shulou(Shulou.com)06/01 Report--

1. Master-slave replication database actual combat environment preparation

The requirement of MySQL master-slave replication practice on the environment is relatively simple, it can be a single single database multi-instance environment, or it can be between two servers, each server deploys an independent database environment. This paper carries on the practice in the environment of single machine database and multi-instance.

two。 Master-slave replication server role definition

Serial number database role database IP information database port information database configuration path 1master192.168.1.203306/data/33062slave1192.168.1.203307/data/33073slave2192.168.1.203308/data/3308

[root@bogon bin] # netstat-lntp | grep 330

Tcp 0 0 0.0.0.0 3306 0.0.0.015 * LISTEN 24215/mysqld

Tcp 0 0 0.0.0.0 3307 0.0.0.015 * LISTEN 24521/mysqld

Tcp 0 0 0.0.0.0 3308 0.0.0.015 * LISTEN 24806/mysqld

3. Perform the following configuration operations on the main library master

3.1. Set the server-id value and enable the binlog function parameters

From the principle of MySQL master-slave replication, we know that to achieve master-slave replication, the key is to turn on the binlog log function, so open the binlog log parameters of the master library.

3.1.1 modify the configuration file / data/3306/my.cnf of the main library, execute vim / data/3306/my.cnf, and modify the parameters as follows:

[mysqld]

Server-id = 1 # the instance server-id used for synchronization cannot be the same

Log-bin = / data/3306/mysql-bin # enable the binlog log function

3.1.2 check the modified parameters:

[root@bogon mysql] # grep-E "server-id | log-bin" / data/3306/my.cnf

Log-bin = / data/3306/mysql-bin

Server-id = 1

3.1.3 restart the main library mysql service

[root@bogon mysql] # / data/3306/mysql restart

3.1.4 Log in to the database and check the modification of the parameters:

Mysql > show variables like 'server_id'

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 1 | |

+-+ +

1 row in set (0.00 sec)

Mysql > show variables like 'log_bin'

+-+ +

| | Variable_name | Value |

+-+ +

| | log_bin | ON |

+-+ +

1 row in set (0.00 sec)

After testing, the binlog function of the main library and its opening

3.2 establish an account on the master database for master-slave replication

According to the principle of master-slave replication, in order to synchronize the slave database with the master database, it is necessary to have an account that can connect to the master database, and this account is established on the master library, and the permission allows the slave library to connect to the master library and synchronize data.

3.2.1 create an account rep in the main database as follows:

Mysql-uroot-p-S / data/3306/mysql.sock # login to the database

Mysql > grant replication slave on *. * to 'rep'@'192.168.1.%' identified by' lb123456'; # create rep account

3.2.2 check account creation

Mysql > use mysql

Mysql > select user,host from user where user='rep'

+-+ +

| | user | host |

+-+ +

| | rep | 192.168.1% |

+-+ +

1 row in set (0.00 sec)

3.3 Lock the table standby database on the main database, view and record the status information of the main database

3.3.1 perform table locking operation on the master database

Mysql > flush tables with read lock

Query OK, 0 rows affected (0.01 sec)

3.3.2 check the status of the main database after locking the table

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000004 | 259 |

+-+

1 row in set (0.00 sec)

Note: you need to record the information displayed by this command, which will be used later from the library.

3.3.3 complete data of the main database. The specific operations are as follows:

[root@bogon /] # mkdir-p / server/backup

[root@bogon /] # mysqldump-uroot-S / data/3306/mysql.sock-- events-A-B | gzip > / server/backup/mysql_bak_$ (date +% F) .sql.gz

[root@bogon /] # cd / server/backup

[root@bogon backup] # ll

Total 144

-rw-r--r--. 1 root root 144803 Jun 14 14:59 mysql_bak_2016-06-14.sql.gz

3.3.4 check the status information of the main database again to ensure that there are no data changes in the database during the data export

[root@bogon backup] # mysql-uroot-S / data/3306/mysql.sock-e "show master status"

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000004 | 259 |

+-+

Note: if the binlog file and location points are consistent, that is, there is no change, it means that the database of the main database has not changed before and after the lock table is exported.

3.3.5 unlock the main library and restore writable

Mysql > unlock tables

Query OK, 0 rows affected (0.00 sec)

4. Perform the following configuration actions on the slave library slave1

4.1. Set the server-id value and turn off the binlog function parameter

The server-id of the slave library is different from that of the master library and other slave libraries, and the binlog parameter configuration of the slave library is annotated.

4.1.1 modify the configuration file / data/3307/my.cnf of the main library, execute vim / data/3307/my.cnf, and modify the parameters as follows:

[mysqld]

Server-id = 2 # the instance server-id used for synchronization cannot be the same

# log-bin = / data/3306/mysql-bin # turn off binlog logging

4.1.2 check the modified parameters:

[root@bogon backup] # grep-E "server-id | log-bin" / data/3307/my.cnf

# log-bin = / data/3307/mysql-bin

Server-id = 2

4.1.3 restart the main library mysql service

[root@bogon mysql] # / data/3307/mysql restart

4.1.4 Log in to the database and check the modification of the parameters:

Mysql > show variables like 'server_id'

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 2 | |

+-+ +

1 row in set (0.00 sec)

Mysql > show variables like 'log_bin'

+-+ +

| | Variable_name | Value |

+-+ +

| | log_bin | OFF |

+-+ +

1 row in set (0.00 sec)

4.2 restore the data exported from the master database to the slave library slave1

[root@bogon /] # cd / server/backup/ # enter the backup directory

[root@bogon backup] # ll

Total 144

-rw-r--r--. 1 root root 144803 Jun 14 14:59 mysql_bak_2016-06-14.sql.gz

[root@bogon backup] # gzip-d mysql_bak_2016-06-14.sql.gz # decompress backup database data

[root@bogon backup] # ll

Total 516

-rw-r--r--. 1 root root 527436 Jun 14 14:59 mysql_bak_2016-06-14.sql

[root@bogon backup] # mysql-uroot-S / data/3307/mysql.sock

Change master to

-> master_host='192.168.1.20'

-> master_port=3306

-> master_user='rep'

-> master_password='lb123456'

-> master_log_file='mysql-bin.000004'

-> master_log_pos=259

Query OK, 0 rows affected (0.16 sec)

4.4 activate the slave synchronization switch to view the replication status

Mysql > start slave

Query OK, 0 rows affected (0.04 sec)

The following three parameters are the most critical to the success of master-slave replication:

[root@bogon backup] # mysql-uroot-S / data/3307/mysql.sock-e "show slave status\ G" | grep-E "IO_Running | SQL_Running | Seconds_Behind_Master"

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0

4.5 Test master-slave replication results

Create a new database in the master database, and then observe the data status of the slave database.

[root@bogon backup] # mysql-uroot-S / data/3306/mysql.sock-e "create database langyabang"; # create a new database 'langyabang' on the main library

[root@bogon backup] # mysql-uroot-S / data/3306/mysql.sock-e "show databases like 'langyabang'"

+-- +

| | Database (langyabang) |

+-- +

| | langyabang |

+-- +

# View the creation of database 'langyabang' on the main database

[root@bogon backup] # mysql-uroot-S / data/3307/mysql.sock-e "show databases like 'langyabang'"

+-- +

| | Database (langyabang) |

+-- +

| | langyabang |

+-- +

# check master-slave replication on slave database

According to the test master and slave are synchronized.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.