MySQL主从复制操作
前期准备:
两台安装Mysql的服务器,或者在一台服务器上部署两个Mysql实例;为了避免出现不必要的错误,Mysql版本请保持一致。
版本查询命令:
mysql>select version();
+------------+
| version() |
+------------+
| 5.7.14-log |
+------------+
1 row in set (0.07 sec)
一、 主库配置:
1.开启二进制日志,配置server_id(需要重启生效)。
[root@Mysql-248 mysql-5.6.30]#cat /etc/my.cnf
[mysqld]
#skip-grant-tables
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
datadir=/data/mysql
basedir=/usr/local/mysql
##open sqllog
general_log=ON
general_log_file=/data/mysql/query.log
#master db
log-bin=/data/bin/mysql-bin
server-id=24
#调整max_allowed_packet
max_allowed_packet = 2000M
log-bin-trust-function-creators=1
#开启事件计划
event_scheduler = 1
#设置保留日志时间
expire_logs_days =3
检验二进制日志状态,ON为打开:
mysql>show variables like 'log_bin' ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
2.在主库建立Mysql复制用户。
mysql>grant replication slave on *.* to'repl_user'@'192.168.10.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql>flush privileges;
Query OK, 0 rows affected (0.00 sec)
3.在主库上锁表备份(锁表是为确保,备份的资料不会再被修改),然后解锁。
锁表,锁表后当前窗口暂时不能关闭:
mysql>flush tables with read lock; //锁表
Query OK, 0 rows affected (0.01 sec)
查看master状态信息:
mysql>show master status;
+------------------+----------+-------------+------------------+-------------------+
| File | Position |Binlog_Do_DB| Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+-------------+------------------+-------------------+
| mysql-bin.000001 | 414 | | | |
+------------------+----------+-------------+------------------+-------------------+
1 row in set (0.00 sec)
新建ssh窗口,备份数据库:
[root@Mysql-248 ~]#mysqldump -uroot -p -A >/tmp/master248.sql
备份完成后,在原先窗口中解锁:
mysql> unlock tables; //解锁
Query OK, 0 rows affected (0.00 sec)
二、从库配置:
1.配置从库server_id与relay-log(需要重启生效)。
注意:server_id必须是唯一的,不能与其他mysql库相同。从库无需开启二进制日志。
# cat /etc/my.cnf
#slave db
log_bin=/data/bin/mysql-bin
server-id=25
relay_log=/data/bin/mysql-relay-bin
log_slave_updates=24
read_only=24
2.将主库的备份拷贝到本机,导入数据库。
拷贝备份:
导入:
# mysql -uroot -p'qwe123``' < /tmp/master248.sql
Warning: Using a password on the command line interfacecan be insecure.
3.指定master服务器信息,开启slave。
指定master信息:
change master to master_host='192.168.10.24',master_user='backup',master_password='backup',master_log_file='mysql-bin.000001',master_log_pos=414;
###
master_host=主服务器IP
master_user=在主服务器上创建的备份用户名
master_password=备份用户密码
master_log_file=查询master(主服务器)的状态得到的File列的值
master_log_pos=Position列的值(master 主机上Position的值)
start slave:启动从服务器复制功能
开启slave:
mysql>start slave;
Query OK, 0 rows affected (0.01 sec)
三、检验主从复制:
1.在从库查询主库信息以及IO进程、SQL进程工作状态。
mysql> show slave status\G;
*************************** 1. row***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.175.248
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 414
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File:mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......
1 row in set (0.00 sec)
查询结果显示
Slave_IO_Running: Yes,
Slave_SQL_Running: Yes,
表示当前主从复制状态正常。
2.测试
在master新建数据库,在slave查询,测试主从复制效果。
Master建库建表。
mysql> create database cubix character set utf8;
Query OK, 1 row affected (0.00 sec)
mysql> use cubix
Database changed
mysql> create table T1 (id int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into T1 VALUES ('1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into T1 VALUES ('2');
Query OK, 1 row affected (0.00 sec)
mysql> insert into T1 VALUES ('3');
Query OK, 1 row affected (0.01 sec)
Slave查询新建的库。
mysql> show databases;
+-------------------+
| Database |
+-------------------+
| information_schema|
| cubix |
| mysql |
| performance_schema|
+-------------------+
6 rows in set (0.00 sec)
mysql> use cubix
Database changed
mysql> show tables;
+----------------+
| Tables_in_cubix|
+----------------+
| T1 |
+----------------+
1 row in set (0.00 sec)
mysql> select * from T1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
检查发现在主库上新增的数据,在从库上也有了,也可以证明主从同步正常。
mysql 日志文件mysql-bin文件清除方法,和mysql-bin相关文件的配置
默认情况下mysql会一直保留mysql-bin文件,这样到一定时候,磁盘可能会被撑满,这时候是否可以删除这些文件呢,是否可以安全删除,是个问题。
首先要说明一下,这些文件都是mysql的日志文件,如果不做主从复制的话,基本上是没用的,虽然没用,但是不建议使用rm命令删除,这样有可能会不安全,正确的方法是通过mysql的命令去删除。
mysql> reset master;
mysql> reset slave;
其实关键的命令就是reset master;这个命令会清空mysql-bin文件。
另外如果你的mysql服务器不需要做主从复制的话,建议通过修改my.cnf文件,来设置不生成这些文件,只要删除my.cnf中的下面一行就可以了。
log-bin=mysql-bin
如果你需要复制,最好控制一下这些日志文件保留的天数,可以通过下面的配置设定日志文件保留的天数:
expire_logs_days = 7
表示保留7天的日志,这样老日志会自动被清理掉
参考文章:
http://blog.csdn.net/minglei922/article/details/19238489