mysql主从复制

mysql主从复制

环境主服务器150,从服务器170

配置150服务器

  • 修改mysql配置文件

    [[email protected] ~]# cat /etc/my.cnf
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    
    # 启用二进制日志,默认在/var/lib/mysql下面,名字随意起
    log-bin=mysqllog
    # 本机数据库ID标识
    server-id=1
    # 可以被从服务器复制的库,需要同步的数据库名称
    binlog-do-db=shopping
    # 不被复制的数据库
    # binlog-ignore-db=aa
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    
  • 创建一个授权用户

    mysql> grant replication slave on *.* to "slave"@"192.168.1.170" identified by "123456";
    
  • 查看主服务器状态

    mysql> show master status;
    +-----------------+----------+--------------+------------------+
    | File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +-----------------+----------+--------------+------------------+
    | mysqllog.000001 |      535 | shopping     |                  |
    +-----------------+----------+--------------+------------------+
    1 row in set (0.00 sec)
    
  • 查看binlog日志

    mysql> show binlog events;
    
  • 备份钱保证两个数据库中的数据保持一致

    [[email protected] ~]# mysqldump -uroot -p -A -E --ignore-table=mysql.events > /opt/all.sql
    
  • copy文件到170从服务器

    scp /opt/all.sql 192.168.1.170:/root
    

配置170服务器

  • 安装mysql服务

    [[email protected] ~]# yum install mysql-server -y
    [[email protected] ~]# service mysqld restart
    
  • 测试能否连接上150服务器的mysql服务

    [[email protected] ~]# mysql -h 192.168.1.150 -u slave -p
    
  • 导入数据到从服务器mysql中

    [[email protected] ~]# mysql -uroot -p < all.sql
    
  • 修改配置文件

    cat /etc/my.cnf
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    
    # 从服务器ID号,如果设置多个从服务器,每个从服务器必须有一个唯一的server-id号
    server-id=2
    # 主服务器的ip地址
    master-host=192.168.1.150
    # 指定在主服务器上可以进行同步的用户名
    master-user=slave
    # 密码
    master-password=123456
    # 断开重连时间
    master-connect-retry=60
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    
  • 重启服务

    [[email protected] ~]# service mysqld restart
    
  • 查看从服务器状态,Slave_IO_RunningSlave_SQL_Running都为yes表示正常

    mysql> show slave status \G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.1.150
                      Master_User: slave
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysqllog.000001
              Read_Master_Log_Pos: 674
                   Relay_Log_File: mysqld-relay-bin.000006
                    Relay_Log_Pos: 250
            Relay_Master_Log_File: mysqllog.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
    
  • 在主服务器中插入数据测试。