N62059第十二周作业

一、主从复制及主主复制的实现
1、主从复制
主节点：
[root@centOS8 ~]# yum -y install mariadb-server
[root@centOS8 ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
server-id=8
log-bin
[root@centOS8 ~]# systemctl restart mariadb
[root@centOS8 ~]# mysql
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 28267 |
| mariadb-bin.000002 | 344 |
+--------------------+-----------+
2 rows in set (0.000 sec)
MariaDB [(none)]> create user 'repluser'@'10.0.0.%';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> grant replication slave on . to 'repluser'@'10.0.0.%' identified by 'P@ssw0rd'
-> ;
Query OK, 0 rows affected (0.001 sec)
从节点：
[root@centOS8 ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
server-id=18
[root@centOS8 ~]# systemctl restart mariadb
[root@centOS8 ~]# mysql
MariaDB [(none)]> help change master to
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.0.0.8', MASTER_USER='repluser', MASTER_PASSWORD='P@ssw0rd', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=344;
Query OK, 0 rows affected (0.007 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.060 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 10.0.0.8
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 344
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mariadb-bin.000002
Slave_IO_Running: Connecting
Slave_SQL_Running: 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: 344
Relay_Log_Space: 256
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'repluser@10.0.0.8:3306' - retry-time: 60 maximum-retries: 86400 message: Can't connect to MySQL server on '10.0.0.8' (113 "No route to host")
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)

2、主主复制
在第一个master节点上实现：
[root@centOS8 ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
server-id=8
log-bin
auto_increment_offset=1
auto_increment_increment=2
[root@centOS8 ~]# systemctl start mariadb
[root@centOS8 ~]# mysql
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 28497 |
| mariadb-bin.000002 | 344 |
+--------------------+-----------+
2 rows in set (0.000 sec)
MariaDB [(none)]> grant replication slave on . to repluser@'10.0.0.%' identified by 'P@ssw0rd';
Query OK, 0 rows affected (0.001 sec)
在第二个master节点上实现
[root@centOS8 ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
server-id=18
log-bin
auto_increment_offset=2
auto_increment_increment=2
[root@centOS8 ~]# systemctl start mariadb
[root@centOS8 ~]# mysql
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.8',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='P@ssw0rd',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mariadb-bin.000002',
-> MASTER_LOG_POS=344;
Query OK, 0 rows affected (0.004 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 28497 |
| mariadb-bin.000002 | 344 |
+--------------------+-----------+
2 rows in set (0.000 sec)
在第一个master节点上实现
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.8',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='P@ssw0rd',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mariadb-bin.000002',
-> MASTER_LOG_POS=476;
Query OK, 0 rows affected (0.014 sec)
第一个节点：
MariaDB [(none)]> create database db1
-> ;
Query OK, 1 row affected (0.025 sec)
MariaDB [(none)]> use db1
Database changed
MariaDB [db1]> create table t1(id int auto_increment primary key,name char(10));
Query OK, 0 rows affected (0.032 sec)
MariaDB [db1]> insert t1 (name) values('user1')
-> ;
Query OK, 1 row affected (0.022 sec)
第二个节点：
MariaDB [db1]> insert t1 (name) values('user2');
Query OK, 1 row affected (0.075 sec)
两个节点同时插入数据：
MariaDB [db1]> insert t1 (name) values('userX');
Query OK, 1 row affected (0.039 sec)
MariaDB [db1]> insert t1 (name) values('userX');
Query OK, 1 row affected (0.027 sec)
MariaDB [db1]> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 1 | user1 |
| 2 | user2 |
| 3 | userX |
| 4 | userX |
+----+-------+
4 rows in set (0.000 sec)

两个节点同时创建数据库：
节点一：
MariaDB [db1]> create database db2;
Query OK, 1 row affected (0.000 sec)
节点二：
MariaDB [db1]> create database db2;
ERROR 1007 (HY000): Can't create database 'db2'; database exists
MariaDB [db1]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.8
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 1997
Relay_Log_File: mariadb-relay-bin.000004
Relay_Log_Pos: 897
Relay_Master_Log_File: mariadb-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: 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: 1997
Relay_Log_Space: 1208
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: 8
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 3
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 4
1 row in set (0.000 sec)

二、xtrabackup实现全量+增量+binlog恢复库
完全备份：
[root@centOS8 ~]# yum -y install mysql
[root@centOS8 ~]# yum -y install percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm
[root@centos8 ~]#xtrabackup -uroot -pmagedu --backup --target-dir=/backup/base
[root@centos8 ~]#xtrabackup -uroot -pmagedu --backup --target-dir=/backup/inc1 --
incremental-basedir=/backup/base
[root@centos8 ~]#xtrabackup -uroot -pmagedu --backup --target-dir=/backup/inc2 --
incremental-basedir=/backup/inc1
还原过程：
[root@centos8 ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base
[root@centos8 ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base
--incremental-dir=/backup/inc1
[root@centos8 ~]#xtrabackup --prepare --target-dir=/backup/base --incrementaldir=/
backup/inc2
[root@centos8 ~]#xtrabackup --copy-back --target-dir=/backup/base
[root@centos8 ~]#chown -R mysql:mysql /var/lib/mysql
[root@centos8 ~]#service mysqld start

三、MyCAT实现MySQL读写分离
创建 MySQL 主从数据库
[root@centOS8 ~]# yum -y install mariadb-server
修改Master和Slave的配置文件
Master:
[root@centOS8 ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
server-id = 1
log-bin

Slave:
[root@centOS8 ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
server-id = 2
[root@centOS8 ~]# systemctl start mariadb
Master创建复制用户
[root@centOS8 ~]# mysql -uroot -p
Enter password:
MariaDB [(none)]> GRANT REPLICATION SLAVE ON . TO 'repluser'@'10.0.0.%' IDENTIFIED BY 'replpass';
Query OK, 0 rows affected (0.089 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000003 | 344 | | |
+--------------------+----------+--------------+------------------+

Slave
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.%',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='replpass',
-> MASTER_LOG_FILE='mariadb-bin.000003',
-> MASTER_LOG_POS=344;
Query OK, 0 rows affected (0.004 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)

在MySQL代理服务器10.0.0.8安装mycat并启动
[root@centOS8 ~]# java -version
openjdk version "1.8.0_312"
OpenJDK Runtime Environment (build 1.8.0_312-b07)
OpenJDK 64-Bit Server VM (build 25.312-b07, mixed mode)
[root@centOS8 ~]# mkdir /apps
[root@centOS8 ~]# tar -xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps/
[root@centOS8 ~]# echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@centOS8 ~]# source /etc/profile.d/mycat.sh
[root@centOS8 ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 127.0.0.1:6010 0.0.0.0:*
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 32 192.168.122.1:53 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 128 [::1]:6010 [::]:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
[root@centOS8 ~]# file /apps/mycat/bin/mycat
/apps/mycat/bin/mycat: POSIX shell script, ASCII text executable
[root@centOS8 ~]# mycat
Usage: /apps/mycat/bin/mycat { console | start | stop | restart | status | dump }
[root@centOS8 ~]# mycat start
Starting Mycat-server...
[root@centOS8 ~]# ss -ntlp
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 127.0.0.1:6010 0.0.0.0:* users:(("sshd",pid=43743,fd=15))
LISTEN 0 1 127.0.0.1:32000 0.0.0.0:* users:(("java",pid=44196,fd=4))
LISTEN 0 128 0.0.0.0:111 0.0.0.0:* users:(("rpcbind",pid=742,fd=4),("systemd",pid=1,fd=164))
LISTEN 0 32 192.168.122.1:53 0.0.0.0:* users:(("dnsmasq",pid=1650,fd=6))
LISTEN 0 128 0.0.0.0:22 0.0.0.0:* users:(("sshd",pid=862,fd=4))
LISTEN 0 5 127.0.0.1:631 0.0.0.0:* users:(("cupsd",pid=864,fd=9))
LISTEN 0 128 [::1]:6010 [::]:* users:(("sshd",pid=43743,fd=14))
LISTEN 0 50 *:45629 : users:(("java",pid=44196,fd=66))
LISTEN 0 50 *:1984 : users:(("java",pid=44196,fd=67))
LISTEN 0 100 *:8066 :
[root@centOS8 ~]# tail /apps/mycat/logs/wrapper.log
STATUS | wrapper | 2022/03/02 15:20:49 | --> Wrapper Started as Daemon
STATUS | wrapper | 2022/03/02 15:20:49 | Launching a JVM...
INFO | jvm 1 | 2022/03/02 15:20:59 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2022/03/02 15:20:59 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2022/03/02 15:20:59 |
INFO | jvm 1 | 2022/03/02 15:21:39 | MyCAT Server startup successfully. see logs in logs/mycat.log
[root@centOS8 ~]# mysql -uroot -p123456 -h 10.0.0.8 -P8066
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.002 sec)
MySQL [TESTDB]> show tables;
+------------------+
| Tables in TESTDB |
+------------------+
| address |
| travelrecord |
+------------------+
2 rows in set (0.000 sec)
MySQL [TESTDB]> select * from travelrecord ;
ERROR 1105 (HY000): backend connect: java.lang.IllegalArgumentException: Invalid DataSource:0

在mycat 服务器上修改server.xml文件配置Mycat的连接信息
[root@centOS8 ~]# vim /apps/mycat/conf/server.xml

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 216,372评论 6赞 498
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,368评论 3赞 392
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 162,415评论 0赞 353
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,157评论 1赞 292
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,171评论 6赞 388
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,125评论 1赞 297
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,028评论 3赞 417
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,887评论 0赞 274
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,310评论 1赞 310
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,533评论 2赞 332
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,690评论 1赞 348
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,411评论 5赞 343
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,004评论 3赞 325
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,659评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,812评论 1赞 268
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,693评论 2赞 368
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,577评论 2赞 353

推荐阅读更多精彩内容