mariadb实战

实验前准备（xiaoguo已测）

① iptables -F && setenforce 清空防火墙策略，关闭selinux
② 拿两台服务器都使用yum 方式安装Mysql 服务，要求版本一致
③ 分别启动两台服务器mysql

实战一：mariadb实现数据库简单一主多从复制实战

TIM截图20180718201301.png

1.环境准备

centos 系统服务器3 台、一台用户做Mysql 主服务器，2台用于做Mysql 从服务器，
配置好yum 源、 防火墙关闭、各节点时钟服务同步、各节点之间可以通过主机名互相通信
机器名称            IP配置          服务角色        备注
master      10.0.0.20          主数据库       二进制日志
slave1      10.0.0.21          从数据库       中继日志
slave2      10.0.0.22          从数据库       中继制日志

cat >> /etc/hosts  <<eof
10.0.0.20  master         
10.0.0.21  slave1  
10.0.0.22  slave2
eof

2、在主master 主服务器上

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上

① 进行完全备份(master)
mkdir /backup
mysqldump -uroot --all-databases > /backup/mysql-all-backup-`date +%F-%T`.sql
把备份生成的文件发给salve-mysql2机器上
scp /backup/mysql-all-backup-2018-07-18-07\:43\:25.sql root@10.0.0.22:/
② 查看现在的二进制文件状态，记录下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;


进行master的完全备份恢复
mysql -uroot -p < mysql-all-backup-2018-07-18-07\:43\:25.sql
systemctl restart mariadb 开启服务

启动从服务器复制线程
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、解除主从关系，恢复独立的服务器

① MariaDB [(none)]> stop slave; 关闭两个线程
② vim /etc/my.cnf 删除3行
relay-log =mysql-relay-log
read-only = 1
log_slave_updates = 1
③ systemctl restart mariadb 重启服务
重置slave
stop slave
reset slave

总结：用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]