mysql主从复制

主服务器IP：192.168.99.10

从服务器IP：192.168.99.20
(一)安装mysql(主从服务器操作相同)

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

增加组和用户

#/usr/sbin/groupadd mysql        
#/usr/sbin/useradd -g mysql mysql -s /sbin/nologin

解压、编译、安装
#tar xvf mysql-5.1.35.tar.gz
#cd mysql-5.1.35
#./configure –prefix=/usr/local/mysql  –localstatedir=/data/mysql/data –with-extra-charsets=utf8,gb2312,gbk –with-pthread –enable-thread-safe-client

#make && make install

 拷贝配置文件

#cp support-files/my-large.cnf   /etc/my.cnf

修改权限

#chown -R mysql.mysql /usr/local/mysql

初始化数据库

 #/usr/local/mysql/bin/mysql_install_db –user=mysql

改变权限

#chown -R mysql:mysql /data/mysql/data

(二)修改配置文件

主服务器

server-id = 1

从服务器

server-id = 2..3..4依次增加

(三)启动服务(主从相同)

#/usr/local/mysql/bin/mysqld_safe –user=mysql&

(四)授权(仅主服务器)
mysql>GRANT REPLICATION SLAVE ON *.* to ‘rep’@’192.168.99.%’ identified by ‘sasasa’;

 (五)查询主数据库状态(主服务器上)

mysql> show master status;

+——————+———-+————–+——————+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000003 |      771 |              |                  |
+——————+———-+————–+——————+

记住file和position的值

(六)配置从服务器

 mysql> change master to master_host=’192.168.99.10′, master_user=’rep’, master_password=’sasasa1′, master_log_file=’mysql-bin.000003′, master_log_pos=771;

master_host主服务器ip，master_user连接帐户,master_password连接密码,maser_log_file填入上面的file值，master_log_pos填入上面的position值

mysql> start slave;        启用了复制功能

mysql> show slave status\G;        检查slave状态

 *************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.99.10
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 771
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000003
             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: 771
              Relay_Log_Space: 410
              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:
 

注意：Slave_IO_Running: Yes  和 Slave_SQL_Running: Yes  如果这两个状态不是yes，那么请检查上面的配置是否正确，主要是file和position设置是否正确，从库是否能用主库的账号登陆 站长教学网 eduyo.com

如果主服务器还有数据传输，那么先进行锁表操作 mysql> FLUSH TABLES WITH READ LOCK; 然后按照第五步的进行操作

  (责任编辑：ken)