實(shí)驗(yàn)前準(zhǔn)備（xiaoguo已測(cè)）
① iptables -F && setenforce 清空防火墻策略律姨，關(guān)閉selinux
② 拿兩臺(tái)服務(wù)器都使用yum 方式安裝Mysql 服務(wù)扯饶，要求版本一致
③ 分別啟動(dòng)兩臺(tái)服務(wù)器mysql
實(shí)戰(zhàn)一：mariadb實(shí)現(xiàn)數(shù)據(jù)庫(kù)簡(jiǎn)單一主多從復(fù)制實(shí)戰(zhàn)
TIM截圖20180718201301.png
1.環(huán)境準(zhǔn)備
centos 系統(tǒng)服務(wù)器3 臺(tái)扮匠、一臺(tái)用戶做Mysql 主服務(wù)器吠架，2臺(tái)用于做Mysql 從服務(wù)器民晒，
配置好yum 源精居、 防火墻關(guān)閉、各節(jié)點(diǎn)時(shí)鐘服務(wù)同步潜必、各節(jié)點(diǎn)之間可以通過主機(jī)名互相通信
機(jī)器名稱 IP配置 服務(wù)角色 備注
master 10.0.0.20 主數(shù)據(jù)庫(kù) 二進(jìn)制日志
slave1 10.0.0.21 從數(shù)據(jù)庫(kù) 中繼日志
slave2 10.0.0.22 從數(shù)據(jù)庫(kù) 中繼制日志
cat >> /etc/hosts <<eof
10.0.0.20 master
10.0.0.21 slave1
10.0.0.22 slave2
eof
2靴姿、在主master 主服務(wù)器上
sed -i '/^\[mysqld\]/a server-id=1' /etc/my.cnf
sed -i '/^\[mysqld\]/a log-bin=/var/lib/mysql/mysql-bin' /etc/my.cnf
mysql -uroot -proot
show master status;
grant replication slave,replication client on *.* to slave@'%' identified by 'root';
show master status;
3、在從slave1上
sed -i '/^\[mysqld\]/a server-id=2' /etc/my.cnf
sed -i '/^\[mysqld\]/a relay_log = /var/lib/mysql/mysql-relay-bin' /etc/my.cnf
sed -i '/^\[mysqld\]/a read_only = 1' /etc/my.cnf
sed -i '/^\[mysqld\]/a log_slave_updates = 1' /etc/my.cnf
#sed -i '/^\[mysqld\]/a log_bin = /var/lib/mysql/mysql-bin' /etc/my.cnf
systemctl start mariadb
mysql -uroot
MariaDB [(none)]> change master to master_host='10.0.0.20', \
master_user='slave',\
master_password='root', \
master_log_file='mysql-bin.000001', \
master_log_pos=245;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status \G;
4磁滚、在從slave2上
① 進(jìn)行完全備份(master)
mkdir /backup
mysqldump -uroot --all-databases > /backup/mysql-all-backup-`date +%F-%T`.sql
把備份生成的文件發(fā)給salve-mysql2機(jī)器上
scp /backup/mysql-all-backup-2018-07-18-07\:43\:25.sql root@10.0.0.22:/
② 查看現(xiàn)在的二進(jìn)制文件狀態(tài)佛吓，記錄下mysql-bin0000?和pos 下面有用(master)
show master status ;
sed -i '/^\[mysqld\]/a server-id=3' /etc/my.cnf
sed -i '/^\[mysqld\]/a relay_log = /var/lib/mysql/mysql-relay-bin' /etc/my.cnf
sed -i '/^\[mysqld\]/a read_only = 1' /etc/my.cnf
sed -i '/^\[mysqld\]/a log_slave_updates = 1' /etc/my.cnf
#sed -i '/^\[mysqld\]/a log_bin = /var/lib/mysql/mysql-bin' /etc/my.cnf
chown mysql.mysql mysql-all-backup-2018-07-18-07\:43\:25.sql
MariaDB [(none)]> show master status;
進(jìn)行master的完全備份恢復(fù)
mysql -uroot -p < mysql-all-backup-2018-07-18-07\:43\:25.sql
systemctl restart mariadb 開啟服務(wù)
啟動(dòng)從服務(wù)器復(fù)制線程
MariaDB [(none)]> change master to master_host='10.0.0.20',\
master_user='slave',\
master_password='root', \
master_log_file='mysql-bin.000001', \
master_log_pos=998;
start slave;
show slave status \G
7、解除主從關(guān)系垂攘，恢復(fù)獨(dú)立的服務(wù)器
① MariaDB [(none)]> stop slave; 關(guān)閉兩個(gè)線程
② vim /etc/my.cnf 刪除3行
relay-log =mysql-relay-log
read-only = 1
log_slave_updates = 1
③ systemctl restart mariadb 重啟服務(wù)
重置slave
stop slave
reset slave
總結(jié)：用show master status 查看binlog和pos 從slave有用维雇。
mariadb查看變量
SHOW VARIABLES LIKE '%size%';
SHOW GLOBAL VARIABLES LIKE '%size%';
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
