查看: 598|回复: 2

[普通教程] MySQL主从同步

签到天数: 1 天

连续签到: 1 天

[LV.1]初来乍到

396 主题	307 好友	5万积分

第三章

听着时间涓滴败血

积分: 51214

发表于 2012-5-19 04:44:25 | 显示全部楼层 |阅读模式

╱人◕‿‿◕人╲定下契约

您需要登录才可以下载或查看，没有账号？╱人◕‿‿◕人╲订下契约（注册新用户）

x

所采用的mysql数据库是rpm包方式安装
1. Master:修改/etc/my.cnf文件[mysqld]
[mysqld]
server-id = 1
log-bin
innodb_flush_log_at_trx_commit=1
sync_binlog=1
datadir=/var/lib/mysql
2. Slave:修改/etc/my.cnf文件[mysqld]
[mysqld]
server-id=2
datadir=/var/lib/mysql
3. Master:在master数据库设置用来同步的slave用户权限
GRANT REPLICATION SLAVE ON *.*
TO '<slave_username>'@'<slave_ip>'
IDENTIFIED BY '<slave_password>';

例: grant replication slave on *.* to 'repl'@'192.168.1.2' identified by '123456';
4.Master上锁表：flush tables with read lock；
         解锁：unlock tables；
5. Master数据导到slave
打包/var/lib/mysql目录，然后scp拷贝到从库上，覆盖从库/var/lib/mysql目录

6. Master查看日志节点和Position信息

查看主库信息获得日志文件以及position

mysql> show master status;
+------------------+------------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+------------+--------------+------------------+
| mysql-bin.000596 | 1050931156 |             |                |
+------------------+------------+--------------+------------------+
7.从库同步
mysql>STOP SLAVE；
mysql>CHANGE MASTER TO
   >MASTER_HOST='<master_host>',
   >MASTER_USER='<slave_username>',
   >MASTER_PASSWORD='<slave_password>',
   >MASTER_LOG_FILE='<see xtrabackup_binlog_info>',
   >MASTER_LOG_POS=<see xtrabackup_binlog_info>;
mysql>START SLAVE;

执行完上述命令后

mysql> show slave status \G
*************************** 1. row ***************************
         Slave_IO_State: Waiting for master to send event
            Master_Host: 192.168.1.2
            Master_User: repl
            Master_Port: 3306
            Connect_Retry: 60
         Master_Log_File: mysql-bin.000596
      Read_Master_Log_Pos: 1036007396
         Relay_Log_File: localhost-relay-bin.000221
            Relay_Log_Pos: 1036007533
   Relay_Master_Log_File: mysql-bin.000596
         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: 1036007396
         Relay_Log_Space: 1036007533
         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
1 row in set (0.00 sec)

同步完成