mysql主从同步

一、前期准备

1. 服务器
   centos6.4 master 192.168.20.113 主
   centos6.4 backup 192.168.20.112 从
2. mysql
   mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64.tar.gz 版本

二 、mysql安装

1.对于centos系统，首先需要卸载掉它自带的mysql

查看 rpm –qa | grep mysql
卸载 rpm  -e –-nodeps 存在的mysql版本

2.下载mysql source code 开始编译安装

yum  -y  install  cmake  gcc-c++  ncurses-devel

3.创建mysql 组 和用户

groupadd mysql    创建mysql组
useradd –g mysql mysql   创建mysql用户

4.解压文件 tar –xzvf xxxx
5.将解压好的文件移到/usr/local 下并命名为mysql 并且转为mysql组和用户

mv mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64 /usr/local/    复制
cd /usr/local/                                                   
mv mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64/ mysql         重命名
chown -R mysql:mysql mysql/                                      更改

6.配置

1.  cd mysql/            到达mysql目录
2.  ./scripts/mysql_install_db --user=mysql        安装mysql
3.  cp ./my.cnf /etc/my.cnf     将my.cnf文件拷贝到/etc目录下供配置
4.  cp ./support-files/mysql.server /etc/init.d/mysqld   将mysql.server 拷贝到/etc/init.d下面供启动
5.  service mysqld start      启动mysql
6.  ./bin mysql               进入mysql
7.  set passwrod = password('root');     更改数据密码
8.  GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION; 创建远程连接
9.  flush privileges         及时生效

三、配置主服务器192.168.20.113

1.mysql -u root -p   登录
2.use mysql          更改数据库
3.grant replication slave on *.* to 'root'@'192.168.20.112' identified by 'root'; 　授权用户root只能从192.168.20.112这个地址访问主服务器192.168.20.113的数据库，并且只具有数据库备份的权限
4.flush privileges; 及时生效
5.vim /etc/my.cnf   打开配置文件
6.  server-id=1  
    log-bin=mysql-bin  #启动MySQL二进制日志系统，
    binlog-do-db=wuyang#需要同步的数据库
    binlog-ignore-db=mysql  #不同步mysql系统数据库，若还有其它不想同步的，继续添加
7.service mysqld restart   重启mysql
8.bin/mysql -u root -p     登录
9.show master status;      查看状态
10.出现
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      120 | wuyang       | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+

四、配置从服务器192.168.20.112

1.vim /etc/my.cnf  打开配置文件
2.  server-id=2  #设置从服务器id，必须于主服务器不同
    log-bin=mysql-bin  #启动MySQ二进制日志系统
    replicate-do-db=ourneeddb  #需要同步的数据库名
    replicate-ignore-db=mysql  #不同步mysql系统数据库
3.service mysqld restart 重启
4.bin/mysql  -u root -p      登录mysql
5.use mysql              使用mysql
6.stop slave;         
7.  change master to
    master_host='192.168.20.113',
    master_user='root',
    master_password='root',
    master_log_file='mysql-bin.000001', 同上
    master_log_pos=120;           同上
8.start slave;
9.show slave status\G     查看状态
10.出现
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.20.113
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 258
               Relay_Log_File: MysqlBackup-relay-bin.000002
                Relay_Log_Pos: 421
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: wuyang
          Replicate_Ignore_DB: mysql
           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: 258
              Relay_Log_Space: 600
              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: 9088a9e0-e178-11e6-911e-000c299a7180
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           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

验证