MySQL eighth exercise (master-slave copy, read-write separation)

Posted by cordoprod on Wed, 02 Feb 2022 06:35:53 +0100

1. Understand the principle of MySQL master-slave replication.

Mainly based on MySQL Binary log
It mainly includes three threads (2 threads) I/O Threads, 1 individual SQL Thread)
1 , MySQL Record the data changes in the binary log;
2 , Slave take MySQL Copy binary logs to Slave In the relay log of;
3 , Slave Make the event in the relay log once, and reflect the data change to itself( Slave )Database of

2. Complete MySQL master-slave replication.

Main library configuration

1) Set the server ID value and start the binlog log

[root@localhost ~]# vi /etc/my.cnf
[mysqld]
log_bin = mysql-bin
server_id = 120

Restart MySQL

[root@localhost ~]# systemctl restart mysql.server

2) create and authorize users

mysql> create user rep@'192.168.159.%' identified by '123456';
mysql> grant replication slave on *.* to rep@'192.168.159.%';

3) . lock table settings are read-only

mysql> flush tables with read lock;

4) view the status of main library

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      617 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

5) backup database

[root@localhost ~]# mysqldump -uroot -p123456 -A -B | gzip > /tmp/db.sql.gz

6) unlocking

mysql> unlock tables;

7) . transfer the backup of the master database to the slave database

[root@localhost ~]# scp /tmp/db.sql.gz 192.168.159.134:/tmp
root@192.168.159.134's password: 
db.sql.gz

Configuration from library

1) Set the server ID value and close the binlog parameter 

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

[mysqld]
server_id=130
#log_bin = /data/mysql/data/mysql-bin

Restart MySQL

[root@localhost ~]# systemctl restart mysqld

2) , restore database

[root@localhost ~]# zcat /tmp/db.sql.gz | mysql -uroot -p123456

3) master slave synchronization setting 

mysql> change master to
    -> master_host='192.168.159.136',
    -> master_user='rep',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000001',  //Here is the first item in the main library status
    -> master_log_pos=617;                  //This is the second item in the main library status

4) start the slave library synchronization switch

mysql> start slave;

Check status:

mysql> show slave status\G

3. Complete MySQL read / write separation configuration

Installation and deployment of MyCAT(1.6.5)

(mycat download link: Index of /1.6.5/ (mycat.org.cn))

1. Deploy jdk environment

1) . install JDK (JDK has been uploaded)

[root@localhost ~]# mkdir /usr/java
[root@localhost ~]# tar xf jdk-8u311-linux-x64.tar.gz -C /usr/java/

2) . setting environment variables

[root@localhost ~]# vi /etc/profile.d/java.sh
[root@localhost ~]# cat /etc/profile.d/java.sh
export JAVA_HOME=/usr/java/jdk1.8.0_311/
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar

Make environment variables effective

[root@localhost ~]# source /etc/profile.d/java.sh

3) , test

[root@localhost ~]# java -version
java version "1.8.0_311"
Java(TM) SE Runtime Environment (build 1.8.0_311-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.311-b11, mixed mode)

2. Download File

1) Download the corresponding tar installation package and the corresponding jar package

[root@localhost ~]# wget -c http://dl.mycat.org.cn/1.6.5/Mycat-server-1.6.5-release-20180122220033-linux.tar.gz

2) , decompression

[root@localhost ~]# tar -xf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz -C /usr/local/

3) Add environment variables

[root@localhost ~]# cd /usr/local/
[root@localhost local]# echo "export PATH=$PATH:/usr/local/mycat/bin" > /etc/profile.d/mycat.sh
[root@localhost local]# source /etc/profile.d/mycat.sh

4) Prepare the environment (one master and one slave perform the following operations to initialize mysql, and MySQL installed by rpm is available)

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# rm -rf /var/lib/mysql/* 
[root@localhost ~]# mysqld --initialize --datadir=/var/lib / / / initialize
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# grep -i password /var/log/mysqld.log / / find the password after initialization

5) . configure master-slave

Main library

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              | sys,mysql        |                   |
+------------------+----------+--------------+------------------+-------------------+

#Create user
mysql> grant replication slave on *.* to rep@'192.168.159.%' identified by '123456';

#Specify a database that does not need to be synchronized
[root@localhost ~]# vi /etc/my.cnf
binlog_ignore_db=sys
binlog_ignore_db=mysql

[root@localhost ~]# systemctl restart mysqld

Slave Library

mysql> change master to 
    -> master_host='192.168.159.135', 
    -> master_user='rep', 
    -> master_password='123456' , 
    -> MASTER_AUTO_POSITION=0;

mysql> start slave;   

mysql> show slave status \G   
 
#Both are yes
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

6. Configure read write separation

1) Modify the configuration file on mycat side

[root@localhost ~]# cd /usr/local/mycat/conf/

[root@localhost conf]# cp schema.xml{,.bak}


[root@localhost conf]# vim schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        </schema>
        <dataNode name="dn1" dataHost="localhost1" database="db1" />
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.159.135:3306" user="mycat"
                                   password="123456">
                        <readHost host="hostS2" url="192.168.159.134:3306" user="mycat_r" password="123456" />
                </writeHost>
        </dataHost>
</mycat:schema>

2) . create db1 database in main database

mysql> create database db1;

3) , create user

Main library upper pair mycat User authorization is as follows: 
mysql> grant insert,delete,update,select on db1.* to mycat@'192.168.159.%' identified by '123456';

MYCAT from library_ R user authorization is as follows:

mysql> grant select on db1.* to mycat_r@'192.168.159.%' identified by '123456';

4) Modify the server on the tomcat side XML file (use the default)

[root@localhost conf]# vim server.xml 
 
① Two users who can connect to the main library are configured here
User: root  password: 123456  Give this user TESTDB Permission to add, delete, modify and query the database.
User: user password: user  Give this user TESTDB Database read permissions.

5) , start mycat

Method 1: # mycat console #<= adopt console Command start mycat , which makes it easy to extract information
Method 2: # mycat start
Method 3: # startup_nowrap.sh # Service script startup 
[root@localhost ~]# mycat console
[root@localhost ~]# netstat -lnupt | egrep "(8|9)066"
tcp6       0      0 :::8066                 :::*                    LISTEN      1913/java           
tcp6       0      0 :::9066                 :::*                    LISTEN      1913/java

6) connect the mysql main database server at the management end

[root@localhost ~]# mysql -uroot -p123456 -P8066 -DTESTDB -h192.168.159.136

7) Master slave synchronous read-write separation test

Main library create table : 
mysql> use db1
mysql> CREATE TABLE test1 (id int(10),name varchar(10),address varchar(20) DEFAULT NULL);

Insert data at the management side:

mysql> insert into test1 values(1,'test1','master');
mysql> insert into test1 values(2,'test1','slave1');
mysql> insert into test1 values(3,'test1','slave2');

verification:

Management side login port 9066

[root@localhost ~]# mysql -uroot -p123456 -P9066 -DTESTDB -h192.168.159.136

mysql> show @@database;   #Displays the list of mycat databases, corresponding to scehma Logical library of XML configuration
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+

mysql> show @@datanode;  #)Displays the list of mycat data nodes, corresponding to scehma dataNode node of XML configuration file
+------+----------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| NAME | DATHOST        | INDEX | TYPE  | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
+------+----------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| dn1  | localhost1/db1 |     0 | mysql |      0 |   10 | 1000 |     139 |          0 |        0 |       0 |            -1 |
+------+----------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+

ysql> show @@heartbeat;  #View heartbeat detection
+--------+-------+-----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME   | TYPE  | HOST            | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |
+--------+-------+-----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| hostM1 | mysql | 192.168.159.135 | 3306 |       1 |     0 | idle   |       0 | 2,2,2        | 2022-01-30 23:11:32 | false |
| hostS2 | mysql | 192.168.159.134 | 3306 |       1 |     0 | idle   |       0 | 1,2,2        | 2022-01-30 23:11:32 | false |
+--------+-------+-----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
View all commands
mysql> show @@help ;
Get current mycat Version of
mysql> show @@version ;
display mycat Front end connection status
mysql> show @@connection ;
display mycat Backend connection status
mysql> show @@backend ;
Display data source
mysql> show @@datasource

Topics: Database MySQL

Hot Topics

©2024 Programmer Think