mysql主从数据库备份
主库
1.配置文件 /etc/mysql/mysql.cnf 加
-
-
[mysqld]
log-bin=mysql-bin
server-id=2 #从库需要设置2以外的其他值
binlog-ignore-db=information_schema #忽略的数据库
binlog-ignore-db=sys #忽略的数据库
binlog-ignore-db=mysql #忽略的数据库
binlog-do-db=we_database #需要同步的数据库
注意：需要添加在[mysqld]下
2.重启数据库
service mysqld restart 或者 sudo /etc/init.d/mysql restart （根据自己情况选用）
3.进入mysql
-
-
GRANT FILE ON *.* TO 'root'@'192.168.1.2' IDENTIFIED BY 'password';#授权
GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.1.2' IDENTIFIED BY 'password';
FLUSH PRIVILEGES #刷新权限
4.查看master状态
-
-
mysql> show master status ;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000004 | 5393 | we_datebase | information_schema,performance_schema,sys,mysql | |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
从库
1.配置从库文件
-
-
log-bin=mysql-bin
server-id=3
binlog-ignore-db=information_schema
binlog-ignore-db=sys
binlog-ignore-db=mysql
replicate-do-db=we_database
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60
2. 重启数据库（如上，不重述）
3. 进入mysql添加主库信息
mysql> stop slave;
mysql> change master to master_host="186.104.0.1",master_user="root",master_password="password", master_log_file="mysql-bin.000004",master_log_pos=0;
mysql> start slave;
mysql> show slave status; #查看slave状态，为Slave_IO_State: Waiting for master to send event则表示正常
原文地址 https://blog.csdn.net/sinat_29325027/article/details/81111389
