mysql 主从备份设置

在主库和从库都创建好的情况下

登录主库数据库

1. 在主库建立从库用户

CREATE USER 'xxxx'@'%' IDENTIFIED BY 'xxxxx';

        %：允许所有host访问，个人根据需求更改

2. 设置主库的从库用户只读权限和从库链接权限

GRANT SELECT ON (database name).(table name) TO 'xxxx'@'%' ;
GRANT REPLICATION SLAVE ON (database name).(table name) TO  'xxxx'@'%'  identified by 'xxxxx';

        databes name: 数据库名称，所有库填 * 

        table name：表名称，所有表 填 *

3.刷新主库权限

        

flush privileges;

4.增加主从配置映射

        # vi /etc/my.cnf #添加如下参数

[mysqld]
server-id=200 #数据库ID值，唯一的，一般用本机的IP地址主机位
binlog-do-db=school_platform #设置从服务器可以同步的库test，有多个就写多行
log-bin=mysqld-bin   #开启binlog日志，必须开启

        重启主库数据库

5.如果需要同步的数据库已存在，可以先导出，后面直接导入从库，这样保证同步起点一致

        

mysqldump -h127.0.0.1 -uxxxx -p -P3306  --databases (database name) >xxxx.sql

6.登录从库创建数据库

CREATE DATABASE `xxxxxx` CHARACTER SET utf8 COLLATE utf8_general_ci;

7.把主库导出的数据库导入从库

source xxxxx.sql

8.配置从库

        

# vi /etc/my.cnf #添加如下参数

log-bin=mysqld-bin #开启binlog日志

server_id=201 #数据库ID号 不能和主库一样

report-host=localhost #向主服务器报告自己主机名，主服务器查询时会知道是哪个从服务器

report-host=192.168.100.201  #向主服务器报告自己IP，主服务器查询时会知道是哪个从服务器

slave-net-timeout=60 #主从网络中断时，等待60秒自动连接

        重启从库数据库

8.获取主库状态

show master status; #显示主数据库服务器的状态

拿到以下参数：

binlog日志           Postion字段里的偏移量
mysql-bin.000030 |      120

10.登录从库，配置从库master信息

change master to
master_host="xxxx", #主数据库的IP地址
master_port=3306， #主库端口
master_user="xxxxx", #主数据库上授权的用户
master_password="xxxx", #授权用户的密码
mastr_log_file="mysqld-bin.000030" #上一步中File字段里的binlog日志
master_log_pos=120; #上一步中Postion字段里的偏移量

11. 启动从库slave

#启动slave
start slave;
#停止slave
stop slave;
# 查看slave 状态
show slave status \G;

mysql> START SLAVE;
Query OK, 0 rows affected (0.08 sec)


mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.42.129
                  Master_User: replicate
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: replicate-bin.000001
          Read_Master_Log_Pos: 1309
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 287
        Relay_Master_Log_File: replicate-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1309
              Relay_Log_Space: 464
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 129
                  Master_UUID: c8baa742-b6c8-11e6-bac3-000c295183f1
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

        以上情况则配置成功；

12. 验证

mysql> show slave hosts;

+-----------+-----------------+------+-------------------+-----------+

| Server_id | Host | Port | Rpl_recovery_rank | Master_id |

+-----------+-----------------+------+-------------------+-----------+

| 201 | 192.168.100.201 | 3306 | 0 | 200  |

+-----------+-----------------+------+-------------------+-----------+

修改主库的数据，看从库是否更新

13.常见问题

        -如果 从库连接不上主库，先手动在从库所在服务器连接一下主库，看地址、账号、密码是否正确。如果能连接 则 检查 change master to中设置的参数，master_log_file 和 master_log_pos必须和主库 show master status; #显示主数据库服务器的状态一直。

        - 检查主库从库用户权限，必须是 REPLICATION SLAVE