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;

 

posted @ 2016-12-23 09:48  vicowong  阅读(485)  评论(0编辑  收藏  举报