第十二周

原创

冯永庆的博客 2021-11-28 21:51:48 ©著作权

文章标签 mysql centos 数据库增量备份 sed 文章分类 运维

©著作权归作者所有：来自51CTO博客作者冯永庆的博客的原创作品，请联系作者获取转载授权，否则将追究法律责任

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

主节点
my.cnf
symbolic-links=0
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/data/mysql/mysqld.pid
log-bin=/data/mysql/mysql-bin
server-id=128
[client]
socket=/data/mysql/mysql.sock

mysql>GRANT REPLICATION SLAVE ON *.* TO ‘repluser’@’192.168.253.%’ IDENTIFIED BY ‘Huawei12#$’;
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |     10491 |
| mysql-bin.000003 |       177 |
| mysql-bin.000004 |       448 |
+------------------+-----------+

备节点
修改uuid 主备不一致
vi /data/mysql/auto.cnf 
[auto]
server-uuid=922b5420-4a7c-11ec-825d-000c2945d239

my.cnf
symbolic-links=0
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/data/mysql/mysqld.pid
log-bin=/data/mysql/mysql-bin
server-id=129
read_only=on
relay_log=relay-log
relay_log_index=relay-log.index

 systemctl restart mysqld


mysql> help change master to;
Name: 'CHANGE MASTER TO'

CHANGE MASTER TO
  MASTER_HOST='source2.example.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='password',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='source2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;

主主复制：
server-id=128使用同一个

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

wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.23/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm

yum -y install percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm

1）预准备：确保数据一致，提交完成的事务，回滚未完成的事务 [root@centos7 ~]#xtrabackup --prepare --target-dir=/back 2）复制到数据库目录注意：数据库目录必须为空，MySQL服务不能启动 [root@centos7 ~]#xtrabackup --copy-back --target-dir=/back 3）还原属性 [root@centos7 ~]#chown -R mysql:mysql /data/mysql 4）启动服务 [root@centos7 ~]#systemctl start mysqld

增量+binlog还原

xtrabackup -uroot -pHuawei12#$ --backup --target-dir=/back/inc1 --incremental-basedir=/back xtrabackup -uroot -pHuawei12#$ --backup --target-dir=/back/inc2 --incremental-basedir=/back

[root@centos7 ~]#xtrabackup --prepare --apply-log-only --target-dir=/back 2）合并第1次增量备份到完全备份 [root@centos7 ~]#xtrabackup --prepare --apply-log-only --target-dir=/back --incremental-dir=/back/inc1 3）合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only [root@centos7 ~]#xtrabackup --prepare --target-dir=/back --incrementaldir=/back/inc2 4）复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动 [root@centos7 ~]#xtrabackup --copy-back --target-dir=/back 5）还原属性：

chown -R mysql.mysql /data/mysql

6）启动服务： [root@centos7 ~]#systemctl start mysqld


#### 3、MyCAT实现MySQL读写分离

./bin/mycat start

[root@128 ~]# cat /root/mycat/conf/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" randomDataNode="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>