MySQL主从复制

1:查看防火状态
    systemctl status firewalld
    service  iptables status
2:暂时关闭防火墙
    systemctl stop firewalld
    service  iptables stop
3:永久关闭防火墙
    systemctl disable firewalld
    chkconfig iptables off
4:重启防火墙
    systemctl enable firewalld
    service iptables restart 

       [mysqld]
       log-bin=mysql-bin   //[必须]启用二进制日志
       server-id=222      //[必须]服务器唯一ID，默认是1，一般取IP最后一段

       [mysqld]
       log-bin=mysql-bin   //[不是必须]启用二进制日志
       server-id=226      //[必须]服务器唯一ID，默认是1，一般取IP最后一段

/etc/init.d/mysqld restart

@H_726_40@mysql>GRANT ReplicatION SLAVE ON *.* to '用户名'@'%' identified by '密码';

@H_726_40@mysql> show master status;　 +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000015 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 seC)

@H_726_40@mysql>change master to master_host='182.92.209.212',master_user='刚刚创建的用户名',master_password='密码',master_log_file='mysql-bin.000015',master_log_pos=154;

@H_726_40@mysql> start slave;

 mysql> show slave statusG

*************************** 1. row ***************************
               Slave_IO_State: WaiTing for master to send event
                  Master_Host: 182.92.209.212
                  Master_User: zhixi
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 154
               Relay_Log_File: zhizuo-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000015
             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: 154
              Relay_Log_Space: 528
              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: 202
                  Master_UUID: eb116683-ed0d-11eb-8a01-00163e361c72
             Master_Info_File: /www/server/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiTing for more @R_262_9531@es
           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)

@H_726_40@mysql> create database db01; Query OK, 1 row affected (0.00 seC) mysql> use db01; Database changed mysql> create table db_tb(id int(3),name char(10)); Query OK, 0 rows affected (0.02 seC) mysql> insert into db_tb values(001,'bobu'); Query OK, 1 row affected (0.01 seC) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_scheR_663_11845@a | | bjpan | | daishua_zhangzhi | | db01 | | mybd | | mysql | | oss_zhangzhixi | | perfoRMANce_scheR_663_11845@a | | sys | | wordpress | +--------------------+ 10 rows in set (0.00 seC)

@H_726_40@mysql> show databases; +--------------------+ | Database | +--------------------+ | information_scheR_663_11845@a | | db01 | | hi_db | | hi_db1 | | hi_db2 | | mysql | | perfoRMANce_scheR_663_11845@a | | sys | +--------------------+ 8 rows in set (0.00 seC) mysql> use db01; Database changed mysql> show tables; +----------------+ | Tables_in_db01 | +----------------+ | db_tb | +----------------+ 1 row in set (0.00 seC) mysql> SELEct * from db_tb; +------+------+ | id | name | +------+------+ | 1 | bobu | +------+------+ 1 row in set (0.00 seC)