允许远程访问mysql主机

  1. USE mysql; -- 更换数据库
  2. UPDATE USER SET HOST='%' WHERE USER='root';  -- 修改权限
  3. FLUSH PRIVILEGES; -- 使数据库生效

mysql 主从配置

  1. 主服务器开启日志系统
  1. vim /etc/my.cnf
  3. [mysqld]
  4. log-bin=mysql-bin    #开启二进制日志
  5. server-id=1          #设置server-id

  1. 给master数据库开启 slave 主从配置的权限

    1. mysql –uroot –pmysql
    2. GRANT REPLICATION SLAVE ON *.* TO 'root'@'%' identified by 'PAYcsroot@0402';
    3. FLUSH PRIVILEGES;

  2. 查看master状态，记录二进制文件名(mysql-bin.000003)和位置(73)

    1. mysql > SHOW MASTER STATUS;
    2. +------------------+----------+--------------+------------------+
    3. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    4. +------------------+----------+--------------+------------------+
    5. | mysql-bin.000003 | 73       | test         | manual,mysql     |
    6. +------------------+----------+--------------+------------------+

  3. slave数据库指向master数据库地址和日志文件

    1. mysql> CHANGE MASTER TO
    2.  ->     MASTER_HOST='182.92.172.80',
    3.  ->     MASTER_USER='root',
    4.  ->     MASTER_PASSWORD='slavepass',
    5.  ->     MASTER_LOG_FILE='mysql-bin.000003',
    6.  ->     MASTER_LOG_POS=73;

  4. 启动slave 同步进程

    1. mysql>start slave;

  5. 查看slave 状态

    1. mysql> show slave status\G;
    2. *************************** 1. row ***************************
    3.             Slave_IO_State: Waiting for master to send event
    4.                Master_Host: 182.92.172.80
    5.                Master_User: rep1
    6.                Master_Port: 3306
    7.              Connect_Retry: 60
    8.            Master_Log_File: mysql-bin.000013
    9.        Read_Master_Log_Pos: 11662
    10.             Relay_Log_File: mysqld-relay-bin.000022
    11.              Relay_Log_Pos: 11765
    12.      Relay_Master_Log_File: mysql-bin.000013
    13.           Slave_IO_Running: Yes
    14.          Slave_SQL_Running: Yes
    15.            Replicate_Do_DB: 
    16.        Replicate_Ignore_DB: 
    17.      ...