Mariadb数据库设置及操作 一主多从 备份还原（实测笔记）
环境：
系统硬件：vmware vsphere (CPU：2*4核，内存2G，双网卡)
系统版本：CentOS-7-x86_64-Minimal-1611.iso
数据库版本信息 : 10.1.20-MariaDB Source distribution
主服务器的IP：192.168.1.14
从服务器的IP：192.168.1.15
从服务器的IP：192.168.1.16
步骤：
1.准备
三台服务器安装好MariaDB
2.主服务器设置
2.1.修改主机名称
[root@centos ~]# hostnamectl --static set-hostname dbmaster
[root@centos ~]# systemctl restart network
2.2 修改IP地址
[root@centos ~]# vi /etc/sysconfig/network-scripts/ifcfg-eno16780032
找到BOOTPROTO，并且修改（设为静态网址）
BOOTPROTO="static"
在最后添加三行内容（添加本机IP，子网掩码，网关）
IPADDR="192.168.1.14"
NETMASK="255.255.255.0"
GATEWAY="192.168.1.1"
：wq 保存退出
2.3 重启
[root@centos ~]# shutdown -r now
2.4 配置主数据库
[root@dbmaster ~]# vim /etc/my.cnf
查找 log-bin=mysql-bin 行，确保此行生效，并且增加以下内容
log-bin=mysql-bin
log-bin-index = mysql-bin.index
查找 Server-id行，设置好ID（这里设置为1014，可以随意设置，只要唯一即可）
server-id = 1014
：wq 保存退出
重启数据库服务，查看是否正常运行
[root@dbmaster ~]# systemctl restart mysql
[root@dbmaster ~]# systemctl status mysql -l
2.5 添加主从同步用户
[root@dbmaster ~]# mysql -u root -p
输入密码，进入数据库命令行。主服务器上建立同步（REPLICATION SLAVE）帐号 （运行环境不要将密码设置过于简单）
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'q123456';
MariaDB [(none)]> FLUSH PRIVILEGES;
增加三个测试数据库 t1 t2 t3
MariaDB [(none)]> show databases;
MariaDB [(none)]> create database t1;
MariaDB [(none)]> create database t2;
MariaDB [(none)]> create database t3;
MariaDB [(none)]> show databases;
查看主服务器状态
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000027 | 460 | | |
+------------------+----------+--------------+------------------+
3.从服务器01设置
3.1.修改主机名称
[root@centos ~]# hostnamectl --static set-hostname dbslave01
[root@centos ~]# systemctl restart network
3.2 修改IP地址
[root@centos ~]# vi /etc/sysconfig/network-scripts/ifcfg-eno16780032
找到BOOTPROTO，并且修改（设为静态网址）
BOOTPROTO="static"
在最后添加三行内容（添加本机IP，子网掩码，网关）
IPADDR="192.168.1.15"
NETMASK="255.255.255.0"
GATEWAY="192.168.1.1"
：wq 保存退出
3.3 重启
[root@centos ~]# shutdown -r now
3.4 配置从数据库01
[root@dbslave01 ~]# vim /etc/my.cnf
查找 log-bin行，禁止日志文件生成
#log-bin=mysql-bin
查找以下 binlog_format=mixed ，在下面添加以下内容
relay-log = relay-log
relay-log-index = relay-log.index
查找 Server-id行，设置好ID（这里设置为1015，可以随意设置，只要唯一即可）
server-id = 1015
在此行下，添加以下内容
replicate-wild-ignore-table=mysql.%
log-slave-updates
read-only = ON
：wq 保存退出
重启数据库服务，查看是否正常运行
[root@dbslave01 ~]# systemctl restart mysql
[root@dbslave01 ~]# systemctl status mysql -l
3.5 查看从数据库状态
[root@dbslave01 ~]# mysql -u root -p
输入密码，进入数据库命令行。查看数据只否处于只读状态
MariaDB [(none)]> show global variables like 'read%';
+----------------------+---------+
| Variable_name | Value |
+----------------------+---------+
| read_buffer_size | 1048576 |
| read_only | ON |
| read_rnd_buffer_size | 4194304 |
+----------------------+---------+
*** 如有需要，可以临时设置数据库可写
MariaDB [(none)]> set global read_only=1;
*****************************************************************************************************************************************
创建仅指定数据库可以访问的用户，(通过my.cnf将从服务器配置为只读模式. 在只读模式下, 只有super权限的用户和slave同步线程才能写入.)
MariaDB [(none)]>GRANT ALL PRIVILEGES ON DatabaseName.* TO 'wroot'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
*****************************************************************************************************************************************
3.6 将主数据库的现有表，全部复制到从数据库
3.6.1 进入主数据库，如果需要可以为mysqldump增加软连接
[root@dbmaster ~]# ll /opt/mysql/bin/mysqldump
[root@dbmaster ~]# ln -s /opt/mysql/bin/mysqldump /bin
3.6.2 在主服务器生成一个快照版本（生成之后，不要再进行数据库改动，否则同步不上）
查看InnoDB所有的数据都同步到磁盘
[root@dbmaster ~] mysql -u root -p
MariaDB [(none)]> show engine innodb status;
[root@dbmaster ~] cd /data
如果只有INNODB,就用如下命令生成快照：
[root@dbmaster ~]# mysqldump -uroot -p --events --triggers --routines --flush-logs --master-data=2 --lock-all-tables --databases t1 t2 t3> dbbackup.sql
--single-transaction 这个参数只对innodb适用，实现热备InnoDB表；因此，不需要同时使用--lock-all-tables；
--databases 后面跟除mysql以后的其他所有数据库的库名 (测试库上只有 t1 t2 t3 三个库)
--master-data 参数会记录导出快照时候的mysql二进制日志位置，一会会用到。
--lock-all-tables 为所有表加读锁
*** 如果有MYISAM或者既有MYISAM又有INNODB，就用如下命令生成快照
mysqldump -uroot -p --events --triggers --routines --flush-logs --master-data=2 --lock-tables --databases test > dbbackup.sql
查看主服务器状态
[root@dbmaster ~]# mysql -u root -p
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000027 | 460 | | |
+------------------+----------+--------------+------------------+
3.6.3 将主数据库的快照版本还原到从服务器01上
[root@dbmaster ~]# scp /data/dbbackup.sql root@192.168.1.15:/data
3.6.4 打开从服务器01，查看是否已经上传成功
[root@dbslave01 ~]# cd /data
[root@dbslave01 ~]# ll
使用grep命令查找到二进制日志的名称以及位置（显示的信息，应该必须和主服务器当前的 show master status 的一致）
[root@dbslave01 ~]# grep -i "change master" dbbackup.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000027', MASTER_LOG_POS=460;
[root@dbslave01 ~]# mysql -u root -p
输入密码，进入数据库命令行。将主数据库快照恢复数据从数据库01中
[root@dbslave01 ~]# show databases;
[root@dbslave01 ~]# source /data/dbbackup.sql
[root@dbslave01 ~]# show databases;
设置从服务器01，同步主服务器
[root@dbslave01 ~]# change master to master_host='192.168.1.14',master_user='mysync',master_password='q123456',master_log_file='mysql-bin.000027',master_log_pos=460;
说明：
master_host 指定主服务器IP地址
master_user 主服务器专用于同步的用户名称（之前在主服务器上设置的）
master_password 主服务器专用于同步的用户密码
master_log_file 主服务器当前日志文件名称（必须对应）
master_log_pos 主服务器当前日志位置（必须对应）
启用从服务器01的同步
[root@dbslave01 ~]# start slave;
查看状态
[root@dbslave01 ~]# show slave status\G:
查看Slave_IO_Running和Slave_SQL_Running的状态，如果都为Yes，那么就成功了。
*** 如果设置错误，可以先停止同步状态，然后清空同步状态 ，再做一次
停止同步状态
[root@dbslave01 ~]# stop slave;
清空同步状态（清空后，同步的日志名称、日志位置都会为空，要注意）
[root@dbslave01 ~]# reset slave;
****************
4 测试主服务，从服务器01的数据库同步
4.1 打开主服务器
[root@dbmaster ~]# mysql -u root -p
删除 t1 数据库，增加 t4 数据库
MariaDB [(none)]> show databases;
MariaDB [(none)]> drop database t1;
MariaDB [(none)]> create database t4;
MariaDB [(none)]> show databases;
查看主服务器状态（注意当前日志文件名称，日志位置）
MariaDB [(none)]> show master status;
4.2 打开从服务器
[root@dbslave01 ~]# mysql -u root -p
查看从服务器01上的数据库
MariaDB [(none)]> show databases;
查看从服务器状态
MariaDB [(none)]> show slave status \G;
特别留意下面两行，是否对应主服务器当前的名称和位置 (另外需要注意，是否有额外错误行信息，如果一切正常，将不会有错误信息）
Master_Log_File: mysql-bin.000027
Read_Master_Log_Pos: 460
5.从服务器02设置
(从服务器02操作与从服务器02一样)
***
同步查看要点，查看主数据库的show master status 以及各从服务器 show slave status\G，相对应的当前日志文件名称和位置是否对应，各从服务器show slave status\G是否有错误信息。
有可能出现同步错误的地方，主数据器和从服务器的安装始数据不一致（包括数据库，表，行数据等）。（例如同步情况下，主数据删除表，此表在从数据库中不存在，则会出现错误）
一旦 show slave status\G，出现错误信息。可以按以下操作执行。
1.主数据库设置为只读。
MariaDB [(none)]> set global read_only=1;
2.生成主服务器的数据库快照，并上传到从服务器
[root@dbslave01 ~]# cd /data
[root@dbslave01 ~]# rm -rf dbbackup.sql
[root@dbslave01 ~]# mysqldump -uroot -p --events --triggers --routines --flush-logs --master-data=2 --lock-all-tables --databases t1 t2 t3> dbbackup.sq
[root@dbslave01 ~]# scp /data/dbbackup.sql root@192.168.1.15:/data
MariaDB [(none)]> show master status;
3.从服务器的数据库停止同步状态，并进行同步状态重设
MariaDB [(none)]>stop slave;
MariaDB [(none)]>reset slave;
4.将主数据库快照，恢复到从服务器上
[root@dbslave01 ~]# cd /data
[root@dbslave01 ~]# grep -i "change master" dbbackup.sql
[root@dbslave01 ~]# source /data/dbbackup.sql
5.重新设置从服务器的数据库同步
[root@dbslave01 ~]# change master to master_host='192.168.1.14',master_user='mysync',master_password='q123456',master_log_file='mysql-bin.000027',master_log_pos=460;
6.启动从服务器的同步,查看各服务器状态是否正常
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G:
7.主数据库设置为可写。
MariaDB [(none)]> set global read_only=0;
