centOS6.5下mysql的主从同步

一、主从同步

1、 开始主从同步，首先关闭2边的mysql服务器

# service mysqld stop

 

2、编辑/etc/my.cnf

主：  

bind-address=192.168.32.57 server-id = 1 log_bin = mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M relay-log=mysql-relay-bin log_slave_updates=on auto_increment_increment=2 auto_increment_offset=1

从：

server-id = 2 auto_increment_increment=2 auto_increment_offset=2 skip-slave-start=true read_only=ON relay-log=relay-bin relay-log-index=relay-bin.index

 

8、开启master的mysql服务并创建用户，让slave服务器连接

1. # service mysqld start 2. mysql>create user mysql@'%' IDENTIFIED BY 'mysql';  //创建用户和密码 3. mysql>GRANT ALL  on *.* to mysql@'%';//这里可根据自己的数据库和权限进行设置   4. grant replication slave, replication client on *.* to 'mysql'@'192.168.1.246' identified by 'mysql'; 5. IP地址可用*表示,用以所有ip都可以连接 6. 注解:简单创建用户，授予REPLICATION SLAVE权限。访问限制，密码，用户名等,根据实际情况各自设置 7. 192.168.1.246是从库的地址 8. mysql为远程同步密码

9、获取master日志坐标

设置读锁 mysql> flush tables with read lock; 查看日志坐标 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 |     1047 |              |                  |                   | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 记住上面两个字段数值(file和position)

 

 

10、导出mastsr所有数据

# mysqldump --all-databases --master-data -uroot -p > /tmp/dbdump.db 然后解锁 mysql> unlock tables; 然后把dbdump.db 拷贝到slave服务器的tmp目录下   //如果没有创建关于mysql的快捷命令，需要先创建，如下：     首先得知道mysql命令或mysqldump命令的完整路径，可以使用find命令查找 除非你知道mysql安装路径可以略过这一步。 find  /  -name  mysql  –print 例如我的mysql的路径是：/usr/local/mysql/bin/mysql,然后映射一个链接到/usr/bin目录下，相当于建立一个链接文件 ln -fs /usr/local/mysql/bin/mysql  /usr/bin mysqldump同理 其中/usr/local/mysql/是mysql的安装路径。

 

11、启动slave服务器并设置主从日志同步

# service mysqld start mysql> CHANGE MASTER TO MASTER_HOST='192.168.32.57', MASTER_USER='mysql’, MASTER_PASSWORD='mysql', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=1047;

 

12、将/tmp/dbdump.db 导入slave数据库

# mysql -uroot -p < /tmp/dbdump.db 或者登入mysql后用source命令导入

 

13、查看slave数据库状态

mysql> start slave; mysql> show slave status \G; *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.32.57                   Master_User: test                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000002           Read_Master_Log_Pos: 1047                Relay_Log_File: relay-bin.000004                 Relay_Log_Pos: 1260         Relay_Master_Log_File: mysql-bin.000002              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: 1047               Relay_Log_Space: 1627               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: 0c66039d-1457-11e7-9954-000c29121cbd              Master_Info_File: /var/lib/mysql/master.info                     SQL_Delay: 0           SQL_Remaining_Delay: NULL       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates            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          Replicate_Rewrite_DB:                  Channel_Name:            Master_TLS_Version: 1 row in set (0.00 sec)   ERROR: No query specified 看到 Slave_IO_Running: Yes Slave_SQL_Running: Yes 同步成功!

 

 

14、验证同步

主：

mysql> create database test; mysql> use test; mysql> create table test(id int(10) not null,name char(10)); mysql> insert into test values (1,'tom');

 

从：

mysql> show databases; mysql> show tables; mysql> use test; mysql> select * from test;