Mysql的主从同步

Mysql的主从同步

1.为什么要实现主从复制

2.环境的介绍
系统环境：centos7.0
客户端连接工具:xshell
远程文件传输工具:xftp
服务器:两台安装好jdk的linux服务器（一主 一从）

3.安装Mysql

基于rpm实现MySQL的安装

#查看有没有安装MySQL
rpm -qa | grep -i mysql
#卸载
rpm -e --nodeps mysql-community-client-5.7.28-1.el7.x86_64
#查找（查询全部文件 name中带有mysql的文件）
find / -name mysql
#删除存在的MySQL文件
rm -rf /etc/selinux/targeted/active/modules/100/mysql /usr/lib64/mysql /usr/share/mysql
#安装server
rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm --force --nodeps
（force查看安装进度 nodeps查看安装条件）
#安装客户端
rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm --force --nodeps

4.登录Mysql

#  登录命令
mysql -u root -p
# 出现报错
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
# 错误原因 密码错误
# 解决办法 免密登录
# 修改配置文件
vi /etc/my.cnf
# 在mysqlld下面 加上跳过授权代码
skip-grant-tables
#重启MySQL的服务
service mysqld restart
#继续登录
mysql -u root -p
Enter password:(这个地方不用输入任何东西直接回车)
#其实免密登录时不安全的,所以进来之后一定要设置密码
#刷新系统权限表
flush PRIVILEGES;
#重新设置密码
#alter user 'root'@'localhost' identified by '123456';
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
#刷新系统权限表
flush PRIVILEGES;
#退出mysql 将免密登录注释掉
exit; 

5.实现Mysql 的主从复制

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

#添加完之后需要登录主服务器给从服务器授权
grant replication slave on *.* to 'root'@'服务器地址.%' 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

然后我们发现我们的的mysql中并没有一个数据库 我们去创建一个数据库

#创建数据库
create database ddm;
#查询数据库
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ddm                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
 #退出MySQL
mysql> exit;

配置完之后重启mysql服务器
service mysqlld restart；

6.验证主从复制

 #登录主服务器
 mysql -u root -p
 #查看主服务器的状态
 mysql> show master status;
 +---------------------+----------+--------------+------------------+-------------------+
 | File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +---------------------+----------+--------------+------------------+-------------------+
 | master-a-bin.000001 |      154 | ddm          |                  |                   |
 +---------------------+----------+--------------+------------------+-------------------+
 1 row in set (0.01 sec)
 #解释对应的一些名词
 #File        生成的日志文件名
 #Position    文件名所处的一个位置(偏移量)
 #Binlog_Do_DB需要实现主从复制的数据库
 #启动服务  
 systemctl mysqld restart
 #登录从服务器
 #设置从服务器如何找到主服务器
 #设置主从复制的日志和偏移量
 change master to master_host='主机ip',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.126.138
                   Master_User: root
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: master-a-bin.000001
           Read_Master_Log_Pos: 154
                Relay_Log_File: localhost-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.126.138:3306' - retry-time: 60  retries: 1
                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: 191119 20:48:42
      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
 使用window系统测试mysql连接
 #需要解决正在连接中的问题
 #使用navicat客户端进行连接,发现连接不上
 #1.防火墙的问题   2.端口未开放    3.未授权
 #可以开放端口3306 需要退出mysql
 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;
 #授权报错
 mysql> grant all privileges on *.* to root@'%' identified by '123456' with grant option;
 ERROR 1819 (HY000): Unknown error 1819
 1819密码不正确
 #查看密码格式
 mysql> select @@validate_password_policy;
 +----------------------------+
 | @@validate_password_policy |
 +----------------------------+
 | MEDIUM                     |
 +----------------------------+
 1 row in set (0.00 sec)
 
 mysql> SHOW VARIABLES LIKE 'validate_password%';
 +--------------------------------------+--------+
 | Variable_name                        | Value  |
 +--------------------------------------+--------+
 | validate_password_check_user_name    | OFF    |
 | validate_password_dictionary_file    |        |
 | validate_password_length             | 8      |
 | validate_password_mixed_case_count   | 1      |
 | validate_password_number_count       | 1      |
 | validate_password_policy             | MEDIUM |
 | validate_password_special_char_count | 1      |
 +--------------------------------------+--------+
 7 rows in set (0.00 sec)
 #修改mysql的密码格式
 mysql> set global validate_password_policy=0;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> set global validate_password_mixed_case_count=0;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> set global validate_password_number_count=3;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> set global validate_password_special_char_count=0;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> set global validate_password_length=3;
 Query OK, 0 rows affected (0.00 sec)
 #再次查看密码状态
 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.00 sec)
 #重新修改密码
 mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
 Query OK, 0 rows affected (0.00 sec)
 #刷新权限
 mysql> flush privileges;
 Query OK, 0 rows affected (0.01 sec)
 #重新授权
 mysql> grant all privileges on *.* to root@'%' identified by '123456' with grant option;
 Query OK, 0 rows affected, 1 warning (0.01 sec)
 #刷新权限
 mysql> flush privileges;
 Query OK, 0 rows affected (0.00 sec)
 
 #重启从服务器并且查看状态
 mysql> stop slave;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> start slave;
 Query OK, 0 rows affected (0.01 sec)
 
 mysql> show slave status\G;
 *************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.126.138
                   Master_User: root
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: master-a-bin.000001
           Read_Master_Log_Pos: 1000
                Relay_Log_File: localhost-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: 1000
               Relay_Log_Space: 1549
               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: 0f900ff3-0ac6-11ea-aac7-000c299a89e6
              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> show databases;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | youruike                |
  | mysql              |
  | performance_schema |
  | sys                |
  +--------------------+
  5 rows in set (0.00 sec)
  
  mysql> use youruike;
  Database changed
  mysql> show tables;
  Empty set (0.00 sec)
  
  mysql> create table user(id int primary key auto_increment,name varchar(20) not null)charset='utf8';
  Query OK, 0 rows affected (0.12 sec)
  
  mysql> show tables;
  +---------------+
  | Tables_in_youruike |
  +---------------+
  | user          |
  +---------------+
  1 row in set (0.00 sec)
  
  mysql> insert into user values(null,'a');
  Query OK, 1 row affected (0.01 sec)
  
  mysql> select * from user;
  +----+------+
  | id | name |
  +----+------+
  |  1 | a    |
  +----+------+
  1 row in set (0.00 sec)
  
  mysql> insert into user values(null,'b');
  Query OK, 1 row affected (0.01 sec)
  
  mysql> insert into user values(null,'c');
  Query OK, 1 row affected (0.00 sec)
  
  mysql> delete from user where id = 3;
  Query OK, 1 row affected (0.00 sec)
  
  mysql> select * from user;
  +----+------+
  | id | name |
  +----+------+
  |  1 | a    |
  |  2 | b    |
  +----+------+
  2 rows in set (0.00 sec)