第12周作業(yè)
MySQL主從復(fù)制及主主復(fù)制的實現(xiàn)
MySQL主從復(fù)制的實現(xiàn)
環(huán)境準備
| 服務(wù)器 | IP | 數(shù)據(jù)庫 | 主從 |
| master | 10.0.0.7 | MariaDB-10.4.22 | 主 |
| slave | 10.0.0.17 | MariaDB-10.4.22 | 從 |
主節(jié)點
修改master主節(jié)點的配置
[root@master ~]#vim /etc/my.cnf
[mysqld]
server_id=7
binlog_format=row
log-bin=/data/logbin/mysql-bin
建立二進制文件存放目錄，修改所有者和所屬組
[root@master ~]#mkdir /data/logbin/ -p
[root@master ~]#chown -R mysql.mysql /data/logbin/
重啟數(shù)據(jù)庫服務(wù)
[root@master ~]#systemctl restart mysqld
數(shù)據(jù)庫完全備份
[root@master ~]#mysqldump -A -F -uroot -pMySQL@2022. --master-data=1 --single-transaction > /data/all.sql
創(chuàng)建復(fù)制用戶并授權(quán)
MariaDB [mysql]> grant replication slave on *.* to repluser@'10.0.0.%' identified by '123456';
將備份復(fù)制到從節(jié)點
[root@master ~]#scp /data/all.sql 10.0.0.17:/data
從節(jié)點
修改slave從節(jié)點的配置
[root@slave ~]#vim /etc/my.cnf
[mysqld]
server_id=17
binlog_format=row
read_only=on
log-bin=/data/logbin/mysql-bin
建立二進制文件存放目錄，修改所有者和所屬組
[root@slave ~]#mkdir /data/logbin/ -p
[root@slave ~]#chown -R mysql.mysql /data/logbin/
重啟數(shù)據(jù)庫服務(wù)
[root@slave ~]#systemctl restart mysqld
從節(jié)點修改備份文件
[root@slave ~]#vim /data/all.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.7',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=328;
關(guān)閉二進制日志
MariaDB [mysql]> set sql_log_bin=off;
導(dǎo)入備份數(shù)據(jù)
MariaDB [mysql]> source /data/all.sql
查看從節(jié)點備份狀態(tài)
MariaDB [mysql]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.0.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 328
Relay_Log_File: centos7-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: No
Slave_SQL_Running: No
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: 328
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: 0
Last_IO_Error:
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_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
開啟從節(jié)點slave進程，從節(jié)點開始復(fù)制
MariaDB [mysql]> start slave;
再次查看從節(jié)點備份狀態(tài)
MariaDB [mysql]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 714
Relay_Log_File: centos7-relay-bin.000002
Relay_Log_Pos: 941
Relay_Master_Log_File: mysql-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: 714
Relay_Log_Space: 1252
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: 7
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: 2
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
查看從節(jié)點的賬號信息及數(shù)據(jù)庫
MariaDB [mysql]> select user,host from mysql.user;
+-------------+-----------+
| User | Host |
+-------------+-----------+
| repluser | 10.0.0.% |
| mariadb.sys | localhost |
| mysql | localhost |
| root | localhost |
+-------------+-----------+
MariaDB [mysql]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
從節(jié)點開啟二進制日志
MariaDB [mysql]> set sql_log_bin=on;
MySQL主主復(fù)制的實現(xiàn)
環(huán)境準備
| 服務(wù)器 | IP | 數(shù)據(jù)庫 |
| master1 | 10.0.0.7 | MariaDB-10.4.22 |
| master2 | 10.0.0.17 | MariaDB-10.4.22 |
master1節(jié)點
修改master1的配置
[root@master1 ~]#cat /etc/my.cnf
[mysqld]
server_id=7
binlog_format=row
log-bin=/data/logbin/mysql-bin
建立二進制文件存放目錄，修改所有者和所屬組
[root@master1 ~]#mkdir /data/logbin/ -p
[root@master1 ~]#chown -R mysql.mysql /data/logbin/
重啟數(shù)據(jù)庫服務(wù)
[root@master1 ~]#systemctl restart mysqld
查看二進制位置
MariaDB [mysql]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 328 |
+------------------+-----------+
創(chuàng)建復(fù)制用戶并授權(quán)
MariaDB [mysql]> grant replication slave on *.* to repluser@'10.0.0.%' identified by '123456';
MariaDB [mysql]> FLUSH PRIVILEGES;
完成備份后再次查看二進制位置
MariaDB [mysql]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 656 |
+------------------+-----------+
確定同步master2的數(shù)據(jù)
MariaDB [mysql]> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.17',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=328;
開啟master1進程，開始復(fù)制
MariaDB [mysql]> start slave;
查看master1備份狀態(tài)
MariaDB [mysql]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.17
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 328
Relay_Log_File: master1-relay-bin.000002
Relay_Log_Pos: 555
Relay_Master_Log_File: mysql-bin.000001
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: 328
Relay_Log_Space: 866
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: 17
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
創(chuàng)建數(shù)據(jù)庫db1
MariaDB [mysql]> create database db1;
查看master2 創(chuàng)建的db2數(shù)據(jù)
MariaDB [mysql]> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| db2 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
master2節(jié)點
修改master2的配置
[root@master2 ~]#cat /etc/my.cnf
[mysqld]
server_id=17
binlog_format=row
log-bin=/data/logbin/mysql-bin
建立二進制文件存放目錄，修改所有者和所屬組
[root@master2 ~]#mkdir /data/logbin/ -p
[root@master2 ~]#chown -R mysql.mysql /data/logbin/
重啟數(shù)據(jù)庫服務(wù)
[root@master2 ~]#systemctl restart mysqld
查看二進制位置
MariaDB [mysql]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 328 |
+------------------+-----------+
確定同步master1的數(shù)據(jù)
MariaDB [mysql]> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.7',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=328;
查看master2備份狀態(tài)
MariaDB [mysql]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.0.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 328
Relay_Log_File: master2-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
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: 328
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: 0
Last_IO_Error:
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_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
開啟master2進程，開始復(fù)制
MariaDB [mysql]> start slave;
再次查看master2備份狀態(tài)
MariaDB [mysql]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 656
Relay_Log_File: master2-relay-bin.000002
Relay_Log_Pos: 883
Relay_Master_Log_File: mysql-bin.000001
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: 656
Relay_Log_Space: 1194
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: 7
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: 2
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
再次查看二進制位置
MariaDB [mysql]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 328 |
+------------------+-----------+
查看master2的賬號信息
MariaDB [mysql]> select user,host from mysql.user;
+-------------+-----------+
| User | Host |
+-------------+-----------+
| repluser | 10.0.0.% |
| mariadb.sys | localhost |
| mysql | localhost |
| root | localhost |
+-------------+-----------+
查看master1 創(chuàng)建的db1數(shù)據(jù)
MariaDB [mysql]> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
創(chuàng)建db2數(shù)據(jù)庫
MariaDB [mysql]> create database db2;
mariabackup實現(xiàn)完全備份+增量備份+binlog恢復(fù)庫
注：在MariaDB 10.3及更高版本中，建議使用Mariabackup替代 Percona XtraBackup的備份方法。
在MariaDB 10.3及更高版本中，不支持 Percona XtraBackup。
mariabackup工具備份和還原，需要三步實現(xiàn)
- 備份：對數(shù)據(jù)庫做完全或增量備份
- 預(yù)準備： 還原前，先對備份的數(shù)據(jù)，整理至一個臨時目錄
- 還原：將整理好的數(shù)據(jù)，復(fù)制回數(shù)據(jù)庫目錄中
環(huán)境準備
centos7:MariaDB-10.4.22:10.0.0.7:備份
centos7:MariaDB-10.4.22:10.0.0.17:還原
查看備份前的數(shù)據(jù)庫
MariaDB [mysql]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| hellodb2 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
完全備份
在原主機做完全備份到/backup
[root@backup ~]#mkdir /backup
#創(chuàng)建用戶并授權(quán)
MariaDB [mysql]> CREATE USER mariabackup@'localhost' IDENTIFIED BY 'mypassword';
MariaDB [mysql]> GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'mariabackup'@'localhost';
MariaDB [mysql]> FLUSH PRIVILEGES;
[root@backup ~]#mariabackup -umariabackup -pmypassword --backup --target-dir=/backup/base
[root@backup ~]#ll /backup/base/
total 12352
-rw-r----- 1 root root 24576 Aug 8 09:29 aria_log.00000001
-rw-r----- 1 root root 52 Aug 8 09:29 aria_log_control
-rw-r----- 1 root root 324 Aug 8 09:29 backup-my.cnf
drwx------ 2 root root 4096 Aug 8 09:29 hellodb
drwx------ 2 root root 4096 Aug 8 09:29 hellodb2
-rw-r----- 1 root root 976 Aug 8 09:29 ib_buffer_pool
-rw-r----- 1 root root 12582912 Aug 8 09:28 ibdata1
-rw-r----- 1 root root 2560 Aug 8 09:29 ib_logfile0
drwx------ 2 root root 4096 Aug 8 09:29 mysql
drwx------ 2 root root 4096 Aug 8 09:29 performance_schema
-rw-r----- 1 root root 75 Aug 8 09:29 xtrabackup_checkpoints
-rw-r----- 1 root root 444 Aug 8 09:29 xtrabackup_info
第一次修改數(shù)據(jù)
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
MariaDB [hellodb]> insert teachers values(null,'wang',18,'M');
MariaDB [hellodb]> insert teachers values(null,'weiow',18,'M');
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | wang | 18 | M |
| 6 | weiow | 18 | M |
+-----+---------------+-----+--------+
增量備份
第一次增量備份
[root@backup ~]#mariabackup -umariabackup -pmypassword --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
[root@backup ~]#ll /backup/
total 8
drwx------ 6 root root 4096 Aug 8 09:29 base
drwx------ 6 root root 4096 Aug 8 09:31 inc1
第二次修改數(shù)據(jù)
MariaDB [hellodb]> insert teachers values(null,'zhang',19,'M');
MariaDB [hellodb]> insert teachers values(null,'jie',20,'M');
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | wang | 18 | M |
| 6 | weiow | 18 | M |
| 7 | zhang | 19 | M |
| 8 | jie | 20 | M |
+-----+---------------+-----+--------+
第二次增量備份
[root@backup ~]#mariabackup -umariabackup -pmypassword --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
[root@backup ~]#ll /backup/
total 12
drwx------ 6 root root 4096 Aug 8 10:12 base
drwx------ 6 root root 4096 Aug 8 10:20 inc1
drwx------ 6 root root 4096 Aug 8 10:25 inc2
[root@backup ~]#du -sh /backup/*
16M /backup/base
3.0M /backup/inc1
3.0M /backup/inc2
數(shù)據(jù)庫還原
mysql節(jié)點停止數(shù)據(jù)庫服務(wù)
[root@mysql ~]#systemctl stop mysqld
確保datadir數(shù)據(jù)目錄為空
#注：數(shù)據(jù)庫目錄必須為空，否則MySQL服務(wù)不能啟動
[root@mysql ~]#cat /etc/my.cnf
[mysqld]
#datadir=/var/lib/mysql
datadir=/data/mysql
[root@mysql ~]#rm -rf /data/mysql/*
backup節(jié)點開始復(fù)制
#目標主機無需創(chuàng)建/backup目錄,直接復(fù)制目錄本身
[root@backup ~]#scp -r /backup/ 10.0.0.17:/
查看backup節(jié)點復(fù)制的文件
[root@mysql ~]#ll /backup/
total 12
drwx------ 6 root root 4096 Aug 8 11:10 base
drwx------ 6 root root 4096 Aug 8 11:10 inc1
drwx------ 6 root root 4096 Aug 8 11:10 inc2
預(yù)準備完全備份
#準備現(xiàn)有備份以還原到 MariaDB 服務(wù)器
[root@mysql ~]#mariabackup --prepare --target-dir=/backup/base
#合并第1次增量備份到完全備份
[root@mysql ~]#mariabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc1
#合并第2次增量備份到完全備份
[root@mysql ~]#mariabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
復(fù)制到數(shù)據(jù)庫目錄
#注意數(shù)據(jù)庫目錄必須為空，MySQL服務(wù)不能啟動
[root@mysql ~]#mariabackup --copy-back --target-dir=/backup/base
#還原屬性
[root@mysql ~]#chown -R mysql:mysql /data/mysql
#重啟數(shù)據(jù)庫服務(wù)
[root@mysql ~]#systemctl restart mysqld
[root@mysql ~]#du -sh /data/mysql
112M /data/mysql
mysql還原節(jié)點確認數(shù)據(jù)的完整性
MariaDB [mysql]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| hellodb2 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
#注：如果出現(xiàn)這種問題，需要reboot重啟系統(tǒng)
MariaDB [hellodb]> select * from teachers;
ERROR 1932 (42S02): Table 'hellodb.teachers' doesn't exist in engine
[root@mysql ~]#reboot
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | wang | 18 | M |
| 6 | weiow | 18 | M |
| 7 | zhang | 19 | M |
| 8 | jie | 20 | M |
+-----+---------------+-----+--------+
MyCAT實現(xiàn)MySQL讀寫分離
環(huán)境準備：
mycat-server:CentOS7.9-10.0.0.27
mysql-master:CentOS7.9-10.0.0.7:MariaDB-10.4.22 寫節(jié)點
mysql-slave:CentOS7.9-10.0.0.17:MariaDB-10.4.22 讀節(jié)點
關(guān)閉SELinux和防火墻
systemctl stop firewalld
setenforce 0
時間同步
主節(jié)點
修改master主節(jié)點的配置
[root@master ~]#vim /etc/my.cnf
[mysqld]
server_id=7
binlog_format=row
log-bin=/data/logbin/mysql-bin
建立二進制文件存放目錄，修改所有者和所屬組
[root@master ~]#mkdir /data/logbin/ -p
[root@master ~]#chown -R mysql.mysql /data/logbin/
重啟數(shù)據(jù)庫服務(wù)
[root@master ~]#systemctl restart mysqld
查看二進制位置
MariaDB [mysql]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 328 |
+------------------+-----------+
創(chuàng)建復(fù)制用戶并授權(quán)
MariaDB [mysql]> grant replication slave on *.* to repluser@'10.0.0.%' identified by '123456';
MariaDB [mysql]> FLUSH PRIVILEGES;
導(dǎo)入hellodb數(shù)據(jù)庫
MariaDB [mysql]> source /root/hellodb_innodb.sql
啟用通用日志，查看讀寫分離
MariaDB [mysql]> set global general_log=on;
MariaDB [mysql]> show variables like 'general%';
+------------------+------------+
| Variable_name | Value |
+------------------+------------+
| general_log | ON |
| general_log_file | master.log |
+------------------+------------+
#永久保存需要寫到配置文件里
[root@master ~]#vim /etc/my.cnf
[mysqld]
general_log=ON
[root@master ~]#tail -f /data/mysql/master.log
#確認主節(jié)點寫
220227 0:44:58 325 Query update teachers set age=@@server_id where tid=4
#停止從節(jié)點后，確認主節(jié)點開始讀
220227 1:19:02 331 Query select * from students
停止主節(jié)點
[root@master ~]#systemctl stop mysqld
從節(jié)點
修改slave從節(jié)點的配置
[root@slave ~]#vim /etc/my.cnf
[mysqld]
server_id=17
binlog_format=row
read_only=on
log-bin=/data/logbin/mysql-bin
建立二進制文件存放目錄，修改所有者和所屬組
[root@slave ~]#mkdir /data/logbin/ -p
[root@slave ~]#chown -R mysql.mysql /data/logbin/
重啟數(shù)據(jù)庫服務(wù)
[root@slave ~]#systemctl restart mysqld
確定同步master主節(jié)點的數(shù)據(jù)
MariaDB [mysql]> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.7',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=328;
開啟slave從節(jié)點進程，開始復(fù)制
MariaDB [mysql]> start slave;
查看slave從節(jié)點備份狀態(tài)
MariaDB [mysql]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 656
Relay_Log_File: centos7-relay-bin.000002
Relay_Log_Pos: 883
Relay_Master_Log_File: mysql-bin.000001
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: 656
Relay_Log_Space: 1194
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: 7
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: 2
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
查看master主節(jié)點導(dǎo)入的hellodb數(shù)據(jù)庫
MariaDB [hellodb]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
啟用通用日志，查看讀寫分離
MariaDB [mysql]> set global general_log=on;
MariaDB [mysql]> show variables like 'general%';
+------------------+-----------+
| Variable_name | Value |
+------------------+-----------+
| general_log | ON |
| general_log_file | slave.log |
+------------------+-----------+
#永久保存需要寫到配置文件里
[root@master ~]#vim /etc/my.cnf
[mysqld]
general_log=ON
[root@slave ~]#tail -f /data/mysql/slave.log
#確認從節(jié)點讀
220809 0:34:48 447 Query select * from teachers
停止從節(jié)點
[root@slave ~]#systemctl stop mysqld
啟動從節(jié)點
[root@slave ~]#systemctl start mysqld
mycat節(jié)點
環(huán)境準備
[root@mycat ~]#yum -y install java
[root@mycat ~]#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@mycat ~]#mkdir /apps
[root@mycat ~]#[root@mycat ~]#tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps/
[root@mycat ~]#echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@mycat ~]#. /etc/profile.d/mycat.sh
啟動mycat
[root@mycat ~]#mycat
Usage: /apps/mycat/bin/mycat { console | start | stop | restart | status | dump }
#注意: 此步啟動較慢,需要等一會兒,另外如果內(nèi)存太小,會導(dǎo)致無法啟動
[root@mycat ~]#mycat start
Starting Mycat-server...
#可以看到打開多個端口,其中8066端口用于連接MyCAT
[root@mycat ~]#ss -ntlp
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 1 127.0.0.1:32000 *:*users:(("java",pid=2478,fd=4))
LISTEN 0 128 *:22 *:*users:(("sshd",pid=1293,fd=3))
LISTEN 0 100 127.0.0.1:25 *:*users:(("master",pid=1387,fd=13))
LISTEN 0 50 [::]:1984 [::]:*users:(("java",pid=2478,fd=70))
LISTEN 0 128 [::]:8066 [::]:*users:(("java",pid=2478,fd=94))
LISTEN 0 50 [::]:40998 [::]:*users:(("java",pid=2478,fd=69))
LISTEN 0 128 [::]:9066 [::]:*users:(("java",pid=2478,fd=90))
LISTEN 0 50 [::]:37942 [::]:*users:(("java",pid=2478,fd=71))
LISTEN 0 128 [::]:22 [::]:*users:(("sshd",pid=1293,fd=4))
LISTEN 0 100 [::1]:25 [::]:*users:(("master",pid=1387,fd=14))
#查看日志，確定成功，可能需要等一會兒才能看到成功的提示
[root@mycat ~]#tail -f /apps/mycat/logs/wrapper.log
STATUS | wrapper | 2022/08/09 17:17:47 | --> Wrapper Started as Daemon
STATUS | wrapper | 2022/08/09 17:17:47 | Launching a JVM...
INFO | jvm 1 | 2022/08/09 17:17:49 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2022/08/09 17:17:49 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2022/08/09 17:17:49 |
INFO | jvm 1 | 2022/08/09 17:17:59 | MyCAT Server startup successfully. see logs in logs/mycat.log
在mycat 服務(wù)器上修改server.xml文件，配置Mycat的連接信息
[root@mycat ~]#vim /apps/mycat/conf/server.xml
#刪除注釋“<!-- -->”,并修改下面行的8066改為3306
<property name="serverPort">3306</property>
<property name="managerPort">9066</property>
<property name="idleTimeout">300000</property>
<property name="authTimeout">15000</property>
<property name="bindIp">0.0.0.0</property>
<property name="dataNodeIdleCheckPeriod">300000</property> #5 * 60 * 1000L; //連
接空閑檢查 刪除#號后面的部分
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property>
<user name="root" defaultAccount="true"> #連接Mycat的用戶名
<property name="password">MyCAT2022.</property> #連接Mycat的密碼
<property name="schemas">TESTDB</property> #數(shù)據(jù)庫名要和schema.xml相對應(yīng)
修改schema.xml實現(xiàn)讀寫分離策略
[root@mycat ~]#cp /apps/mycat/conf/schema.xml /apps/mycat/conf/schema.xml.bak
[root@mycat ~]#vim /apps/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"
dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="localhost1" database="hellodb" />
<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="10.0.0.7:3306" user="root"
password="123456">
<readHost host="host2" url="10.0.0.17:3306" user="root"
password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
重新啟動mycat
[root@mycat ~]#mycat restart
#查看日志，確定成功
[root@mycat ~]#tail -f /apps/mycat/logs/wrapper.log
INFO | jvm 1 | 2022/08/09 17:25:15 | at io.mycat.config.util.ConfigUtil.getDocument(ConfigUtil.java:115)
INFO | jvm 1 | 2022/08/09 17:25:15 | at io.mycat.config.loader.xml.XMLSchemaLoader.load(XMLSchemaLoader.java:111)
INFO | jvm 1 | 2022/08/09 17:25:15 | ... 13 more
STATUS | wrapper | 2022/08/09 17:25:18 | <-- Wrapper Stopped
STATUS | wrapper | 2022/08/09 17:25:18 | --> Wrapper Started as Daemon
STATUS | wrapper | 2022/08/09 17:26:18 | Launching a JVM...
INFO | jvm 1 | 2022/08/09 17:27:23 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2022/02/26 21:24:25 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2022/02/26 21:24:25 |
INFO | jvm 1 | 2022/02/26 21:24:28 | MyCAT Server startup successfully. see logs in logs/mycat.log
#連接MyCAT的端口已改為3306
[root@mycat ~]#ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 1 127.0.0.1:32000 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 50 [::]:1984 [::]:*
LISTEN 0 100 [::]:3306 [::]:*
LISTEN 0 100 [::]:9066 [::]:*
LISTEN 0 50 [::]:43274 [::]:*
LISTEN 0 50 [::]:37134 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 100 [::1]:25 [::]:*
在Mycat服務(wù)器上連接并測試
[root@mycat ~]#mysql -uroot -pMyCAT2022. -h127.0.0.1 -DTESTDB
MySQL [TESTDB]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
MySQL [TESTDB]> use TESTDB;
MySQL [TESTDB]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 17 |
+-------------+
MySQL [TESTDB]> select @@hostname;
+------------+
| @@hostname |
+------------+
| slave |
+------------+
在主和從服務(wù)器分別啟用通用日志后，查看讀寫分離
MySQL [TESTDB]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
MySQL [TESTDB]> update teachers set age=@@server_id where tid=4;
#確認主節(jié)點寫
MySQL [TESTDB]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 7 | F |
+-----+---------------+-----+--------+
停止從節(jié)點后，MyCAT自動調(diào)度讀請求至主節(jié)點
MySQL [TESTDB]> select * from students;
#停止從節(jié)點后，確認主節(jié)點開始讀
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 7 |
+-------------+
#啟動從節(jié)點后，確認從節(jié)點讀
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 17 |
+-------------+
停止主節(jié)點，MyCAT不會自動調(diào)度寫請求至從節(jié)點
MySQL [TESTDB]> update teachers set age=@@server_id where tid=3;
ERROR 1184 (HY000): java.net.ConnectException: Connection refused
Ansible常用模塊介紹
Command 模塊
功能：在遠程主機執(zhí)行命令，此為默認模塊，可忽略 -m 選項
Shell 模塊
和command相似，用shell執(zhí)行命令，支持各種符號，比如：*,$, >
注意：此模塊不具有冪等性
Script 模塊
功能：在遠程主機上運行ansible服務(wù)器上的腳本(無需執(zhí)行權(quán)限)
注意：此模塊不具有冪等性
Copy 模塊
功能：從ansible服務(wù)器主控端復(fù)制文件到遠程主機
注意: src=file 如果是沒指明路徑，則為當前目錄或當前目錄下的files目錄下的file文件
Get_url 模塊
功能: 用于將文件從http、https或ftp下載到被管理機節(jié)點上
Fetch 模塊
功能：從遠程主機提取文件至ansible的主控端，該模塊的工作原理與[copy]類似，但與之相反，它用于從遠程機器獲取文件，并將它們存儲在本地文件樹中，按主機名組織，目前不支持目錄
File 模塊
功能：設(shè)置文件屬性，創(chuàng)建軟鏈接等
stat 模塊
功能：檢查文件或文件系統(tǒng)的狀態(tài)
unarchive 模塊
功能：解包解壓縮
Archive 模塊
功能：打包壓縮保存在被管理節(jié)點
Hostname 模塊
功能：管理主機名，注意，此模塊不修改“/etc/hosts”。
Cron 模塊
功能：計劃任務(wù)，使用此模塊管理crontab和環(huán)境變量條目。
支持時間：minute，hour，day，month，weekday
Yum 和 Apt 模塊
功能：
yum 管理軟件包，只支持RHEL，CentOS，fedora，不支持Ubuntu其它版本；
apt 模塊管理 Debian 相關(guān)版本的軟件包
Service 模塊
功能：管理服務(wù)，控制遠程主機上的服務(wù)
User 模塊
功能：管理用戶，管理用戶帳戶和用戶屬性
Group 模塊
功能：管理組
Replace 模塊
功能：該模塊有點類似于sed命令，主要也是基于正則表達式進行匹配和替換，建議使用此模塊將替換文件中模式的所有實例
SELinux 模塊
功能：管理 SELInux 策略，配置SELinux模式和策略，使用后可能需要重新啟動
mount 掛載和卸載
功能: 掛載和卸載文件系統(tǒng)，此模塊控制“/etc/fstab”中的活動和配置裝載點。
Setup 模塊
功能： setup 模塊來收集主機的系統(tǒng)信息，這些 facts 信息可以直接以變量的形式使用，但是如果主機較多，會影響執(zhí)行速度，playbooks會自動調(diào)用此模塊，以收集有關(guān)的有用變量可以在劇本中使用的遠程主機
可以使用 gather_facts：no 來禁止 Ansible 收集 facts 信息
debug 模塊
功能：此模塊可以用于輸出信息，并且通過 msg 定制輸出的信息內(nèi)容，該模塊在執(zhí)行期間打印語句，對調(diào)試非常有用
注意：msg后面的變量有時需要加 " " 引起來
浙公網(wǎng)安備 33010602011771號