MySQL主从复制和备份

MySQL主从复制

1、复制类型

1）基于语句的复制。在主服务器上执行的 SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。2）基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍。3）混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

2、复制过程1）主服务器上发现数据有更新会写入一个二进制文件中2）从服务器上开启—个IO线程用于读取主服务器上的二进制日志3）通过I/O线程写入到从服务器上的中继日志中4）从服务器再开启一个SQL线程去执行中继日志中的SQL指令，从而达到复制的效果

流程图：

主配置文件在vim /etc/my.cnf

3、配置master主服务器

授权一个具备复制权限的用户

grant replication slave on *.* to 'Li'@'192.168.88.131' identified by 'p-0p-0p-0';

刷新mysql

flush privileges;

配置mysql文件

vim /etc/my.cnf
......
server-id=10        #主服务器的ID标识
log_bin = master-bin        #主服务器中生成二进制日志的名字前缀
log-slave-updates = true        #允许从服务器来进行读取二进制日志（更新）

重启mysql

systemctl restart mysqld

查看主服务的状态

show master status;            

主服务器状态

+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      154 |              |                |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

注意：

#file 表示生成的二进制文件名#Position 初始定位，默认是154，数据库里面原本有没有数据都是154

4、配置sever从服务器

配置mysql文件

vim /etc/my.cnf
.......
server_id = 20        #从服务器的ID标识
relay-log = relay-log-bin        #中继日志的文件名前缀
relay-log-index = slave-relay-bin.index        #指定初始中继日志文件名

重启mysql

systemctl restart mysqld

从服务器区连接主服务器

change master to
master_host='192.168.88.131',master_user='Li',master_password='p-0p-0p-0',master_log_file='master-bin.000001' ,master _log_pos=154;

注意：

#master_host 主MysQL的IP地址

#master_user 主MysQL上授予的具备复制权限的用户

#master_password 密码

#master_log_file 主MysQL上的二进制文件名

#master_log_pos 主MysQL上的二进制文件定位

启动从mysql

start slave

查看从服务器状态(主要查看IO和SQL状态是否为Yes)

show slave status\G;

从服务器状态

......
Slave_IO_Running: Yes        #I/O线程是否有开启
Slave_SQL_Running: Yes        #SQL线程是否有开启

数据库备份

例：

备份book数据库到/opt目录

mysqldump -h127.0.0.1 -uroot  -ppass book > /opt/backupfile.sql

备份并压缩备份文件

mysqldump -h127.0.0.1 -uroot -ppass book | gzip > /opt/backupfile.sql

同时备份多个数据库

mysqldump -h127.0.0.1 -uroot -ppass --databases book student test > multibackupdile.sql

还原数据库，需要先创建一个数据库

mysql -h127.0.0.1 -uroot -ppass book < backupfile.sql

针对压缩文件进行导入

gunzip < backupfile.sql.gz | mysql -h127.0.0.1 -uroot  -ppass myweb