mysql主从数据库备份

mysql主从数据库备份

主库    

    1.配置文件 /etc/mysql/mysql.cnf 加 

2. [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

2. 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状态

2. 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.配置从库文件

2. 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