Linux第十九周

1 主从复制及主主复制的实现

主从复制

#主节点
[root@master ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=7
log-bin

[root@master ~]#systemctl restart mariadb

[root@master ~]#mysql
#查看二进制文件和位置
MariaDB [(none)]> show master logs;
+-------------------------+-----------+
| Log_name                | File_size |
+-------------------------+-----------+
| mariadb-bin.000001      |       154 |
+-------------------------+-----------+
1 row in set (0.00 sec)

#创建复制用户
MariaDB [(none)]> grant replication slave on *.* to repluser@'172.16.0.%' identified by '123456';

#从节点
[root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=17

[root@slave ~]#systemctl restart mariadb

[root@slave1 ~]#mysql
MariaDB [(none)]> help change master to
MariaDB [(none)]> CHANGE MASTER TO
  MASTER_HOST='172.16.0.7',
  MASTER_USER='repluser',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mariadb-bin.000001',
  MASTER_LOG_POS=154;

MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G

主主复制

#在第一个master节点上实现
[root@master1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=7
log-bin
auto_increment_offset=1
auto_increment_increment=2

[root@master1 ~]#systemctl restart mariadb

[root@master1 ~]#mysql
#查看二进制文件和位置
MariaDB [(none)]> show master logs;
+-------------------------+-----------+
| Log_name                | File_size |
+-------------------------+-----------+
| mariadb-bin.000001      |       154 |
+-------------------------+-----------+
1 row in set (0.00 sec)

#创建复制用户
MariaDB [(none)]> grant replication slave on *.* to repluser@'172.16.0.%' identified by '123456';

#从节点
[rootmaster2 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=17
log-bin
auto_increment_offset=2
auto_increment_increment=2

[rootmaster2 ~]#systemctl restart mariadb

[rootmaster2 ~]#mysql
MariaDB [(none)]> CHANGE MASTER TO
  MASTER_HOST='172.16.0.7',
  MASTER_USER='repluser',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mariadb-bin.000001',
  MASTER_LOG_POS=154;

MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G

2 xtrabackup实现全量+增量+binlog恢复库

[root@centos8 ~]# yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
[root@centos8 ~]# percona-release enable-only tools release
[root@centos8 ~]# percona-release enable-only tools
[root@centos8 ~]# yum install -y percona-xtrabackup-24
#完全备份：
[root@centos8 ~]# mkdir /backup
[root@centos8 ~]# xtrabackup -uroot -p123456 --backup --target-dir=/backup/base
#修改数据
#增量备份
[root@centos8 ~]# xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
#还原数据
[root@centos8 ~]# scp -r /backup/* 目标主机:/backup/
#预准备完成备份
[root@centos8 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base
#合并增量备份到完全备份
[root@centos8 ~]# xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc1
#复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
[root@centos8 ~]# xtrabackup --copy-back --target-dir=/backup/base
#还原属性，启动服务
[root@centos8 ~]# chown -R mysql:mysql /var/lib/mysql
[root@centos8 ~]# systemctl start mysqld

3 MyCAT实现MySQL读写分离

#安装mycat
[root@centos8 ~]# yum -y install java mariadb
[root@centos8 ~]# java -version
[root@centos8 ~]# wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
[root@centos8 ~]# mkdir /apps
[root@centos8 ~]# tar xf 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 ~]# . /etc/profile.d/mycat.sh
[root@centos8 ~]# mycat start
#配置mycat
[root@centos8 ~]# vim /apps/mycat/conf/server.xml
        <user name="root"> #连接Mycat的用户名
                <property name="password">123456</property> #连接Mycat的密码
                <property name="schemas">TESTDB</property> #数据库名要和schema.xml相对应
        </user>
</mycat:server>

[root@centos8 ~]# vim /apps/mycat/conf/schema.xml
<!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="***mycat***" /> #其中mycat表示后端服务器实际的数据库名称
        <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="host1" url="172.16.0.7:3306" user="root" password="123456">
                ***<readHost host="host2" url="172.16.0.17:3306" user="root" password="123456"/>***
                </writeHost>
        </dataHost>
</mycat:schema>
[root@centos8 ~]# mycat restart
#在后端主服务器创建用户并对mycat授权
[root@centos8 ~]#mysql -uroot -p
mysql> create database mycat;
mysql> GRANT ALL ON *.* TO 'root'@'172.16.0.%' IDENTIFIED BY '123456' ;
mysql> flush privileges;
#在Mycat服务器上连接并测试
[root@centos8 ~]#mysql -uroot -p123456 -h127.0.0.1 -P8066
mysql> use TESTDB;
mysql> create table t1(id int);
MySQL> select @@server_id;
MySQL> select @@hostname;