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

First, master-slave introduction

Mysql master and slave is also called Replication and AB replication. To put it simply, after the two machines An and B become masters and slaves, they will write data on A, and the other B will also write data to achieve real-time data synchronization.

Second, the role of master and slave

1. Real-time disaster recovery for failover

2. Read-write separation, providing query service

3. Backup to avoid affecting the business

3. Install mysql on both servers

1. Environmental preparation

Turn off the firewall to SELINUX

[root@yanyinglai ~] # systemctl stop firewalld

[root@yanyinglai ~] # systemctl disable firewalld

[root@yanyinglai] # sed-ri's / (SELINUX=). * /\ 1The ledUniver g'/ etc/selinux/config

[root@yanyinglai ~] # setenforce 0

2. Install mysql

Install dependency packages

[root@yanyinglai ~] # yum-y install ncurses-devel openssl-devel openssl cmake mariadb-devel

3. Create users and groups

[root@yanyinglai] # groupadd-r-g 306 mysql

[root@yanyinglai] # useradd-M-s / sbin/nologin-g 306-u 306 mysql

4. Download the mysql package in binary format

[root@yanyinglai ~] # cd / usr/src/

[root@yanyinglai src] # wget https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

5. Decompress the software to / usr/local/

[root@yanyinglai src] # ls

Debug kernels mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

[root@yanyinglai src] # tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz-C / usr/local/

[root@yanyinglai src] # ls / usr/local/

Bin etc games include lib lib64 libexec mysql-5.7.22-linux-glibc2.12-x86_64 sbin share src

[root@yanyinglai src] # cd / usr/local/

[root@yanyinglai local] # ln-sv mysql-5.7.22-linux-glibc2.12-x86_64/ mysql

"mysql"-> "mysql-5.7.22-linux-glibc2.12-x86_64/"

[root@yanyinglai local] # ll

Total dosage 0

Drwxr-xr-x. 2 root root 6 November 5 2016 bin

Drwxr-xr-x. 2 root root 6 November 5 2016 etc

Drwxr-xr-x. 2 root root 6 November 5 2016 games

Drwxr-xr-x. 2 root root 6 November 5 2016 include

Drwxr-xr-x. 2 root root 6 November 5 2016 lib

Drwxr-xr-x. 2 root root 6 November 5 2016 lib64

Drwxr-xr-x. 2 root root 6 November 5 2016 libexec

Lrwxrwxrwx. 1 root root 36 September 7 22:20 mysql- > mysql-5.7.22-linux-glibc2.12-x86_64/

Drwxr-xr-x. 9 root root 129 September 7 22:19 mysql-5.7.22-linux-glibc2.12-x86_64

Drwxr-xr-x. 2 root root 6 November 5 2016 sbin

Drwxr-xr-x. 5 root root 49 September 3 23:02 share

Drwxr-xr-x. 2 root root 6 November 5 2016 src

6. Modify the master group of the directory / usr/locaal/mysql

[root@yanyinglai local] # chown-R mysql.mysql / usr/local/mysql

[root@yanyinglai local] # ll / usr/local/mysql-d

Lrwxrwxrwx. 1 mysql mysql 36 September 7 22:20 / usr/local/mysql-> mysql-5.7.22-linux-glibc2.12-x86_64/

7. Add environment variables

[root@yanyinglai local] # ls / usr/local/mysql

Bin COPYING docs include lib man README share support-files

[root@yanyinglai local] # cd

[root@yanyinglai ~] # echo 'export PATH=/usr/local/mysql/bin:$PATH' > / etc/profile.d/mysql.sh

[root@yanyinglai] #. / etc/profile.d/mysql.sh

[root@yanyinglai ~] # echo $PATH

/ usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

8. Set up a data storage directory

[root@yanyinglai ~] # cd / usr/local/mysql

[root@yanyinglai mysql] # mkdir / opt/data

[root@yanyinglai mysql] # chown-R mysql.mysql / opt/data/

[root@yanyinglai mysql] # ll / opt/

Total dosage 0

Drwxr-xr-x. 2 mysql mysql 6 September 7 22:25 data

9. Initialize the database

[root@yanyinglai mysql] # / usr/local/mysql/bin/mysqld-initialize-user=mysql-datadir=/opt/data/

/ / A temporary password is generated at the end of this command, where the password is 1EbNA-k*BtKo

10. Configure mysql

[root@yanyinglai ~] # ln-sv / usr/local/mysql/include/ / usr/local/include/mysql

"/ usr/local/include/mysql"-> "/ usr/local/mysql/include/"

[root@yanyinglai ~] # echo'/ usr/local/mysql/lib' > / etc/ld.so.conf.d/mysql.conf

[root@yanyinglai] # ldconfig-v

11. Generate configuration files

[root@yanyinglai ~] # cat > / etc/my.cnf set password = password ('123456')

Query OK, 0 rows affected, 1 warning (0.00 sec)

Mysql > quit

Bye

Third, mysql master-slave configuration

1. Ensure that the data in the slave database is the same as that in the master database.

(1) first create the libraries and tables that need to be synchronized in the main database

[root@yanyinglai] # mysql-uroot-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 4

Server version: 5.7.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. Al

Oracle is a registered trademark of Oracle Corporation and

Affiliates. Other names may be trademarks of their respect

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the curr

A. Create a database yan

Mysql > create database yan

Query OK, 1 row affected (0.00 sec)

B. Create a database lisi

Mysql > create database lisi

Query OK, 1 row affected (0.00 sec)

C. Create database wangwu

Mysql > create database wangwu

Query OK, 1 row affected (0.00 sec)

D, useyan database

Mysql > use yan

Database changed

F. Create a table tom

Mysql > create table tom (id int not null,name varchar (100) not null, age tinyint)

Query OK, 0 rows affected (11.83 sec)

G, the value inserted into the tom table

Mysql > insert tom (id,name,age) values (1 recorder zhangshanqie 20), (2 recorder wangwujia 7), (3 recorder lisification 23)

Query OK, 3 rows affected (0.07 sec)

Records: 3 Duplicates: 0 Warnings: 0

H. Show the tom table

Mysql > select * from tom

+-- +

| | id | name | age | |

+-- +

| | 1 | zhangshan | 20 | |

| | 2 | wangwu | 7 |

| | 3 | lisi | 23 | |

+-- +

3 rows in set (0.00 sec)

(2) back up the main database

When backing up the main library, you need to open another terminal and lock the database to avoid inconsistencies caused by other people writing during the backup.

A. Enter mysql

[root@yanyinglai] # mysql-uroot-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 5

Server version: 5.7.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

B. Close all open tables and lock all tables in all databases using a global read lock

Mysql > flush tables with read lock

Query OK, 0 rows affected (0.76 sec)

/ / the terminal of this locked table can not exit until the backup is completed (exit locking table is invalid)

C. Back up the master library and transfer the backup files to the slave library

[root@yanyinglai] # mysqldump-uroot-p123456-- all-databases > / opt/all-20180907.sql

Mysqldump: [Warning] Using a password on the command line interface can be insecure.

D. List the files in the opt directory

[root@yanyinglai ~] # ls / opt/

All-20180907.sql data

G. Copy the all-20180907.sql file under the / opt directory to the / opt directory of 192.168.1.201

[root@yanyinglai ~] # scp / opt/all-20180907.sql root@192.168.1.201:/opt/

ECDSA key fingerprint is SHA256:7mLj77SFk7sPkhjpMPfdK3nZ98hOuyP4OKzjXeijSJ0.

ECDSA key fingerprint is MD5:a0:1b:eb:7f:f0:b6:7b:73:97:91:4c:f3:b1:89:d8:ea.

Are you sure you want to continue connecting (yes/no)? Yes

Warning: Permanently added '192.168.55.129' (ECDSA) to the list of known hosts.

Root@192.168.55.129's password:

All-20180907.sql 100% 784KB 783.3KB/s 00:01

H. Unlock the table state of the main library and exit the interactive interface directly.

Mysql > quit

Bye

(3) restore the backup of the master database on the slave database and check whether it is consistent with the data of the master database.

[root@yanyinglai] # mysql-uroot-p123456

< /opt/all-20180907.sql mysql: [Warning] Using a password on the command line interface can be insecure. [root@yanyinglai ~]# mysql -uroot -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.22 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

Show databases

+-+

| | Database |

+-+

| | information_schema |

| | lisi |

| | mysql |

| | performance_schema |

| | sys |

| | wangwu |

| | yan |

+-+

7 rows in set (0.18 sec)

Mysql > use yan

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > select * from tom

+-- +

| | id | name | age | |

+-- +

| | 1 | zhangshan | 20 | |

| | 2 | wangwu | 7 |

| | 3 | lisi | 23 | |

+-- +

3 rows in set (0.06 sec)

(4) create a synchronization account in the master database to authorize the use of slave data

[root@yanyinglai] # mysql-uroot-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 7

Server version: 5.7.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

A. Create a user repl with password

Mysql > create user 'repl'@'192.168.1.201' identified by' 123456'

Query OK, 0 rows affected (5.50 sec)

B. Grant copy permission from server 192.168.1.201

Mysql > grant replication slave on. To 'repl'@'192.168.1.200'

Query OK, 0 rows affected (0.04 sec)

C. Grant refresh permission

Mysql > flush privileges

Query OK, 0 rows affected (0.09 sec)

(5) configure the master database to edit the configuration file

[root@yanyinglai ~] # vim / etc/my.cnf

[root@yanyinglai ~] # cat / etc/my.cnf

[mysqld]

Basedir = / usr/local/mysql # basic path

Datadir = / opt/data # data directory

Socket = / tmp/mysql.sock # Interface path

Port = 3306 # Port

Pid-file = / opt/data/mysql.pid # # pid file path

User = mysql

Skip-name-resolve

/ / add the following

Log-bin=mysql-bin / / enable binlog Log

Server-id=1 / / Master database server unique identifier the master must be larger than the slave

Log-error=/opt/data/mysql.log / / error log path

(6) restart mysql service

[root@yanyinglai ~] # service mysqld restart

Shutting down MySQL. SUCCESS!

Starting MySQL.Logging to'/ opt/data/mysql.log'.

.. SUCCESS!

(7) list all open network connection ports

[root@yanyinglai ~] # ss-antl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN 0 128: 22:

LISTEN 0 100 127.0.0.1:25:

LISTEN 0 128: 22:

LISTEN 0 100:: 1:25:

LISTEN 0 80: 3306:

(8) check the status of the main database

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000001 | 154 | |

+-+

1 row in set (0.00 sec)

(9) configure slave database

Edit configuration file

[root@yanyinglai ~] # cat / etc/my.cnf

[mysqld]

Basedir = / usr/local/mysql

Datadir = / opt/data

Socket = / tmp/mysql.sock

Port = 3306

Pid-file = / opt/data/mysql.pid

User = mysql

Skip-name-resolve

/ / add the following:

Server-id=2 / / sets the unique identifier of the slave library that the slave must be smaller than the master

Relay-log=mysql-relay-bin / / enable relay log relay log

Error-log=/opt/data/mysql.log

(10) restart the mysql service of the slave library

[root@yanyinglai ~] # service mysqld restart

Shutting down MySQL.. SUCCESS!

Starting MySQL.. SUCCESS!

[root@yanyinglai ~] # ss-antl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN 0 128: 22:

LISTEN 0 100 127.0.0.1:25:

LISTEN 0 128: 22:

LISTEN 0 100:: 1:25:

LISTEN 0 80: 3306:

(11) configure and start master-slave replication

Mysql > change master to

-> master_host='192.168.1.200'

-> master_user='repl'

-> master_password='123456'

-> master_log_file='mysql-bin.000001'

-> master_log_pos=154

Query OK, 0 rows affected, 2 warnings (0.28 sec)

(13) View the status of the slave server

Mysql > show slave status\ G

1. Row

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.55.130

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 154

Relay_Log_File: mysql-relay-bin.000003

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes / / this must be yes

Slave_SQL_Running: Yes / / this must be yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 154

Relay_Log_Space: 527

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: 5abf1791-b2af-11e8-b6ad-000c2980fbb4

Master_Info_File: / opt/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

ERROR:

No query specified

4. Test and verify that the data is inserted into the tom table of the yan library of the master server:

(1) insert data into the tom table of yan library

Mysql > use yan

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > select * from tom

+-- +

| | id | name | age | |

+-- +

| | 1 | zhangshan | 20 | |

| | 2 | wangwu | 7 |

| | 3 | lisi | 23 | |

+-- +

3 rows in set (0.09 sec)

Mysql > insert tom (id,name,age) value (4, "yyl", 18)

Query OK, 1 row affected (0.14 sec)

Mysql > select * from tom

+-- +

| | id | name | age | |

+-- +

| | 1 | zhangshan | 20 | |

| | 2 | wangwu | 7 |

| | 3 | lisi | 23 | |

| | 4 | yyl | 18 | |

+-- +

4 rows in set (0.00 sec)

(2) check whether the data is synchronized from the database

Mysql > use yan

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > select * from tom

+-- +

| | id | name | age | |

+-- +

| | 1 | zhangshan | 20 | |

| | 2 | wangwu | 7 |

| | 3 | lisi | 23 | |

| | 4 | yyl | 18 | |

+-- +

4 rows in set (0.00 sec)

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.