Detailed deployment of MySQL master-slave in Linux

1. Master-Slave Introduction
In modern enterprises, the data is very important, and the database selection of storage data is very varied, but no matter what kind of database, there is a hidden danger.
Think of a few questions:
Use a database to hold data, if this database server hangs to cause data loss what to do?
Large business volume, more data, access to more people, a database can not guarantee the quality of service to do?

2. master-Slave function
Real-time disaster preparedness for failover
Read and write separation, provide query service
Backup to avoid impacting your business

3. master-Slave form

One Master one from
Primary master replication
One master multiple reads from the---extended system because the read is read from the library
Multi-master one supported from---version 5.7
Replication at the Cascade level

4. Principle of master-slave copying

1. The main library logs all writes to the Binlog log and generates a log dump thread that passes the Binlog log to the I/O thread from the library via the log dump thread
2. Generate two threads from the library, one I/O thread, one SQL thread
The 3.I/O thread goes to request the binlog of the main library and writes the resulting binlog log to the relay log (trunk log) file
4.SQL thread, will read the log in the relay log file, and parse into concrete operation, to achieve the master-slave operation consistent, achieve the final data consistent purpose.

*5. Master-Slave replication configuration
Master-Slave Replication configuration steps:
1. Ensure that the database is the same as the data in the primary database
2. Create a sync account in the main database to be used from the database
3. Configure the primary database (modify the configuration file)
4. Configuration from the database (modify the configuration file)

Environment Description:

Database Roles	IP	application and System versions	There is no data
Primary database	192.168.209.12	centos7/redhat7/mysql-5.7	Countless data
From the database	192.168.209.13	centos7/redhat7/mysql-5.7	Countless data

Requirements: Set up two MySQL servers, one as the primary server 192.168.209.12, one as the 192.168.209.13 from the server, the master server to write operations from the server. Description://Install the mysql-5.7 version on both master and slave servers * * The primary server and the slave server are installed Mysql**********************************mysql installation respectively ********** Shut down the firewall and selinux//installation dependency package [[email protected] ~]# yum-y install Ncurses-devel openssl-devel OpenSSL cmake mariadb-devel//Create users and Groups [[[email protected] ~]# cd/usr/src/[[email protected] src]# groupadd-r-G 306 mysql[[email protected] src]# useradd-m-s/sbin/nologin-g 306-u 306 mysql//download MySQL package in binary format [[Email prot Ected] src]# wget https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz//decompression software to /usr/local[[email protected] src]# tar XF mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz-c/usr/local/[[email  protected] src]# cd/usr/local///Create soft connection [[email protected] local]# ln-sv mysql-5.7.22-linux-glibc2.12-x86_ 64/mysql//modifying the genus/usr/local/mysql of the directory [[email protected] local]# chown-r Mysql.mysql/usr/local/mysQl[[email protected] local]# ll/usr/local/mysql//Add environment variable [[email protected] ~]# Ls/usr/local/mysql[[email  protected] ~]# echo ' export path=/usr/local/mysql/bin: $PATH ' >/etc/profile.d/mysql.sh[[email  Protected] ~]# source/etc/profile.d/mysql.sh[[email protected] ~]# echo $PATH//Set up data storage directory [[email protected] ~]# mkdir/opt/data[[email protected] ~]# chown-r mysql.mysql/opt/data/[[email protected] ~]# ll/opt/total consumption 0dr Wxr-xr-x. 2 MySQL MySQL 6 August 13:20 data//Initialize database Note This command will generate a temporary password to remember jd?ajfrky4pq[[email protected] ~]#/usr/local/mysql/bin/ Mysqld--initialize--user=mysql--datadir=/opt/data///configuration mysql//Soft Connection [[email protected] ~]# ln-sv/usr/local/ mysql/include//usr/local/include/mysql[[email protected] ~]# echo '/usr/local/mysql/lib ' >/etc/ Ld.so.conf.d/mysql.conf[[email protected] ~]# ldconfig-v//Generate configuration file [[email protected] ~]# cat >/etc/ MY.CNF << eof> [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> eof//Configuration service startup script [[email protected] ~]# cp-a/usr/local/mysql/support-files/mysql.server/etc/init.d/ Mysqld[[email protected] ~]# Sed-ri ' s#^ (basedir=). *#\1/usr/local/mysql#g '/etc/init.d/mysqld[[email  Protected] ~]# Sed-ri ' s#^ (datadir=). *#\1/opt/data#g '/etc/init.d/mysqld//start mysql[[email protected] ~]# service                  Mysqld start[[email protected] ~]# ps-ef |grep mysql[[email protected] ~]# ss-antllisten 0 80 ::: 3306:::*//Change password log in using temporary password [[email protected] ~]# MYSQL-UROOT-PJD?AJFRK Y4PQ This is the temporary password for the above steps mysql> set password = password (' Lanzhiyong '), ############ #查看主库有哪些库 ################## #3 [[Email  protected] ~]# mysql-uroot-penter Password://password Set above

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), 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names 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 |
| MySQL |
| Performance_schema |
| SYS |
+--------------------+
4 rows in Set (0.00 sec)

#####################查看从库有哪些库#############[[email protected] ~]# mysql -uroot -pEnter password:

Welcome to the MySQL Monitor. Commands End With; or \g.
Your mysql Connection ID is 3
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 the Oracle Corporation and/or its
affiliates. Other names 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 |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in Set (0.01 sec)

###############现在在主服务上添加四个数据库########mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || lan                || lanzhiyong         || mysql              || performance_schema || sys                || zhi                |+--------------------+7 rows in set (0.00 sec)//进入lanzhiyong数据库里添加一个lanzhi表mysql> use lanzhiyong;Database changedmysql> show tables;+----------------------+| Tables_in_lanzhiyong |+----------------------+| lanzhi               |+----------------------+1 row in set (0.00 sec)//查看表的内容mysql> select * from lanzhi;+----+------+------+| id | name | age  |+----+------+------+|  1 | lan  |   10 ||  2 | cs   |   90 ||  3 | lol  |   66 ||  4 | pp   |   33 |+----+------+------+4 rows in set (0.00 sec)

Fully-prepared Master Library
First exit the previous database, the full master library need to open a separate terminal, and then open a 192.168.209.12 server, to the database plus read lock, to avoid during the backup with others in the write caused by inconsistent data

   Plus read lock can only read can not write mysql> flush tables with read lock; Query OK, 0 rows affected (0.02 sec)//Backup main Library transfers the backup file to the slave library [[email protected] ~]# mysqldump-uroot-p--all-databases > All.sql[[email protected] ~]# SCP all.sql [email protected]:/root/the authenticity of host ' 192.168.209.13 ( 192.168.209.13) ' can ' t be established. ECDSA key fingerprint is SHA256:lOImReX4QGLGm5Qibnn4osotw9PoMtSRGLRaK1JAs4w.ECDSA key fingerprint is MD5 : e4:1a:5f:28:d1:e0:2a:28:50:1a:1e:9c:cd:23:03:9d. Is you sure want to continue connecting (yes/no)? yeswarning:permanently added ' 192.168.209.13 ' (ECDSA) to the list of known hosts.    [email protected] ' s password:all.sql 100% 784KB 24.4mb/s 00:00//Unlock the lock table State of the main library mysql> Quitbye//log in from the library and then read the backup from the main library [[email protected] ~]# mysql-uroot-penter password:mysql > Source all.sql; #读取了之后 all the data in the main library is guaranteed to be consistent from the library//create a sync account in the main database to authorize the use of mysql> create user ' repl ' @ ' 192.168.209.13 from the database' Identified by ' repl123 '; Query OK, 0 rows affected (0.01 sec)//In the primary database is authorized to replicate to mysql> from the database grant replication Slave on *. * to ' repl '; Query OK, 0 rows Affected (0.00 sec)//Refresh permissions mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) ############# #配置主数据库 ###################[[email protected] ~]# vi/etc/my.cnf[ Mysqld]basedir =/usr/local/mysqldatadir =/opt/datasocket =/tmp/mysql.sockport = 3306pid-file =/opt/data/ Mysql.pidlog-error=/opt/data/mysql.log//Add error log directory user = Mysqlskip-name-resolve #replicationlog-bin=mysql_bin//Add enabled Binlog log server-id=3//Add database server Unique identifier, the Server-id value of the main library must be greater than the size from the library [[email protected] ~]# service mysqld restart[              [email protected] ~]# ss-antl State recv-q send-q Local address:port Peer address:port      LISTEN 0 *:22 *:* LISTEN 0      127.0.0.1:25 *:*            LISTEN 0::: $:::* LISTEN 0 : 1:25:::* LISTEN 0 80::: 3 306:::*//View the status of the main library 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) ###### ########### #配置从数据库 ##################[[email protected] ~]# vi/etc/my.cnf[mysqld]basedir =/usr/local/ Mysqldatadir =/opt/datasocket =/tmp/mysql.sockport = 3306pid-file =/opt/data/mysql.pidlog-error=/opt/data/mysql.log      Add error log Directory user = mysqlskip-name-resolve#replicationserver-id=5//Add a unique identifier from the library, the Server-id value from the library must be less than the value of the main library RelA Y-log=mysql_relay_log//Add trunk log file [[[email protected] ~]# service mysqld restart[[email protected] ~]# Ss-an      TL State recv-q send-q Local address:port Peer address:port LISTEN 0                               *:22 *:* LISTEN 0 100 127.0.0.1:25                              *:* LISTEN 0 128::: 22 :::* LISTEN 0:: 1:25:::* LIST EN 0::: 3306:::*//Configure from library and start from replication mysql> change Master to Maste R_host= ' 192.168.209.12 ', master_user= ' repl ', master_password= ' repl123 ', master_log_file= ' mysql_bin.000001 ', MA     ster_log_pos=154; QuEry OK, 0 rows affected, 2 warnings (0.03 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec)//view from server Status mysql> show slave status \g*************************** 1. Row ***************************slave_io_state:waiting for Master to send Eventmaster_host:192.168.209.12master_user: Replmaster_port:3306connect_retry:60master_log_file:mysql_bin.000002read_master_log_pos:154relay_log_file: Mysql_relay_log.000003relay_log_pos:367relay_master_log_file:mysql_bin.000002slave_io_running:yesslave_sql_ Running:YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_ Table:replicate_wild_ignore_table:last_errno:0last_error:skip_counter:0exec_master_log_pos:154relay_log_space: 740until_condition:noneuntil_log_file:until_log_pos:0master_ssl_allowed:nomaster_ssl_ca_file:master_ssl_ca_ Path:master_ssl_cert:master_ssl_cipher:master_ssl_key:seconds_behind_master:0master_ssl_verify_server_cert: Nolast_io_errno:0last_io_error:last_sql_errNo:0last_sql_error:replicate_ignore_server_ids:master_server_id:3master_uuid: 3bcb9f5a-b269-11e8-aeca-000c2947a37dmaster_info_file:/opt/data/master.infosql_delay:0sql_remaining_delay:   Nullslave_sql_running_state:slave have read all relay log; Waiting for more updatesmaster_retry_count:86400master_bind:last_io_error_timestamp:last_sql_error_timestamp: master_ssl_crl:master_ssl_crlpath:retrieved_gtid_set:executed_gtid_set:auto_position:0replicate_rewrite_db: Channel_name:master_tls_version:1 row in Set (0.00 sec) ###### #主库插入记录验证 #########

//Add several records in the Lanzhi table in the Lanzhiyong library in the main library to verify that you can synchronize
mysql> select from Lanzhi;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | lan | |
| 2 | cs | |
| 3 | lol | |
| 4 | pp | |
+----+------+------+
4 rows in Set (0.01 sec)
Mysql> inserts into Lanzhi (Id,name,age) VALUES (5, ' AA ', at $), (6, ' BB ') , (7, ' CC ', 100);
Query OK, 3 rows affected (0.03 sec)
Records:3 duplicates:0 Warnings:
mysql> select from Lanzhi;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | lan | |
| 2 | cs | |
| 3 | lol | 5 |
| 4 | pp | AA | 23 |
| 6 | BB | 45 |
| 7 | CC | 100 |
+----+------+------+
7 rows in Set (0.00 sec)

########### #从库看是否同步 ########## #3
Mysql> select * from Lanzhi;
+----+------+------+
| ID | name | Age |
+----+------+------+
| 1 | LAN | 10 |
| 2 | CS | 90 |
| 3 | lol | 66 |
| 4 | PP | 33 |
| 5 | AA | 23 |
| 6 | BB | 45 |
| 7 | CC | 100 |
+----+------+------+
7 rows in Set (0.01 sec)

Detailed deployment of MySQL master-slave in Linux