mysql实现主从复制

环境介绍

• centos7.0
  使用VMware搭建两台服务器
  192.168.32.132（主）
  192.168.32.133 （从）

-安装MySQL

# 查看有没有MySQL
rpm -qa | grep -i mysql
find / -name mysql
# 删除存在的MySQL文件
 rm -rf /etc/selinux/targeted/active/modules/100/mysql
 rm -rf /usr/lib64/mysql
 rm -rf /usr/share/mysql
 # 安装server
 rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm --force --nodeps
 参数说明：
 i 安装参数  v进度条 h详细信息
 --nodeps就是安装时不检查依赖关系，比如你这个rpm需要A，但是你没装A，这样你的包就装不上，
 用了--nodeps你就能装上了。
 --force就是强制安装，比如你装过这个rpm的版本1，如果你想装这个rpm的版本2，就需要用--force强制安装
 # 安装客户端
 rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm --force --nodeps

登录MySQL

mysql -uroot -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
#原因：刚安装不知道密码
#修改配置文件
vi /etc/my.cnf
#在MySQLd的下面加上
skip-grant-tables
# 重启服务
service mysqld restart
#重新设置密码，免密不安全
#刷新权限表
flush PRIVILEGES;
#重新设置密码
alter user "root"@"localhost" identified by "123456";
#刷新权限表
flush PRIVILEGES;
#修改配置文件，删除免密
vi /etc/my.cnf
# 删除 skip-grant-tables

实现MySQL的主从复制

• 

主服务器配置

# 先配置主服务器
#修改配置文件
vi /etc/my.cnf
# 添加一下内容
# 日志文件的名字
log_bin=master-a-bin
# 日志文件的格式
binlog-format=ROW
#服务器的id（zk的集群），一定是唯一的
server-id=1
#对应需要实现主从复制的数据库
binlog_do_db=gogo  # ---(数据库名字)

# 添加完主服务器需要登陆主服务器给从服务器授权
grant replication slave on *.* to 'root'@'192.168.32.132' identified by '123456';
#刷新权限表
flush PRIVILEGES;

从服务器的配置

#修改配置文件
vi /etc/my.cnf
# 添加一下内容
# 日志文件的名字
log_bin=master-a-bin
# 日志文件的格式
binlog-format=ROW
#服务器的id（zk的集群），一定是唯一的
server-id=2
#双主互相备份（表示从服务器可能是另外一台服务器的主服务器）
#log-slave-updates=true

设置验证主从复制

# 重启主服务器和从服务器
service mysqld restart
# 查看主服务器的状态
mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| master-a-bin.000001 |      154 | gogo         |                  |                   |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 说明
File # 生成的日志文件名
Position # 文件名所处的一个位置（偏移量）
Binlog_Do_DB # 需要实现主从复制的数据库

# 启动服务 systemctl mysqld start

设置从服务器如何找到主服务器

设置主从复制的日志和偏移量
change master to
master_host='192.168.32.132',master_port=3306,master_user='root',master_password='123456',master_log_file='master-a-bin.000001',master_log_pos=154;

# 启动slave的数据同步
start slave;
# 停止slave的数据同步
stop slave;
# 查看salve的配置信息
show slave status\G;

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.32.132
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-a-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: aubin-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master-a-bin.000001
             Slave_IO_Running: Connecting
            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: 154
              Relay_Log_Space: 154
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'root@192.168.32.132:3306' - retry-time: 60  retries: 2
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 200416 18:29:05
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

# 说明
Slave_IO_Running: Connecting  正在连接中
# 解决
# 主服务器设置
# 使用navicat客户端进行连接，连接不上
# 1、防火墙的问题 2、端口未开放 3、未授权
# 可以开放端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
#重新加载防火墙
firewall-cmd --reload
# 授权
# with grant option 不仅仅授予增删改查，还授予权限的权限
grant all privileges on *.* to root@'%' identified by '123456' with grant option;
# 授权报错
ERROR 1819 (HY000): Unknown error 1819
密码格式不对
解决方案：(干掉密码位数等要求)
set global validate_password_policy=0;
set global validate_password_mixed_case_count=0;
set global validate_password_number_count=3;
set global validate_password_special_char_count=0;
set global validate_password_length=3;

mysql> show variables like 'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password_check_user_name    | OFF   |
| validate_password_dictionary_file    |       |
| validate_password_length             | 3     |
| validate_password_mixed_case_count   | 0     |
| validate_password_number_count       | 3     |
| validate_password_policy             | LOW   |
| validate_password_special_char_count | 0     |
+--------------------------------------+-------+
7 rows in set (0.01 sec)

# 刷新权限表
flush privileges;

从服务器操作

# 重启从服务器并且查看状态
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.32.132
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-a-bin.000001
          Read_Master_Log_Pos: 604
               Relay_Log_File: aubin-relay-bin.000003
                Relay_Log_Pos: 323
        Relay_Master_Log_File: master-a-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: 604
              Relay_Log_Space: 1149
              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: 1
                  Master_UUID: 098f7466-7fc1-11ea-8d3c-000c29a958e1
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

测试是否成功

主服务器

mysql> create table user(id int primary key auto_increment,name varchar(20) not null) charset='utf8';
Query OK, 0 rows affected (0.16 sec)

mysql> insert into user values(null,'a');

从服务器

mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.02 sec)