Mysql搭建主从同步

一、1、实验环境：两台服务器搭建主从服务实现同步

       2、实验拓扑：

           主服务器HK63（IP：192.168.2.104）---------从服务器HK64（IP：192.168.2.105）

       3、实验思路：

（1）、准备两台服务器
（2）、两台服务器搭建数据库（数据库版本5.1版本）

（3）、创建要同步的数据库（MA）

（4）、配置主服务器主配置文件开启二进制日志，从服务器无需开启，重启服务

（5）、主服务器授权从服务器访问权限

（6）、从服务器指定主服务器IP地址、用户名、密码、端口号

（7）、主从服务器均关闭selinux及iptables

（8）、进行测试

二、搭建环境

1、主服务器HK63数据库相关配置

（1）、进入服务器Hk63数据库创建需同步的数据库MA

mysql> show databases;

+--------------------+

| Database |

+--------------------+|

information_schema |

| mysql |

| test |

+--------------------+

3 rows in set (0.10 sec)

mysql> create database MA;Query OK, 1 row affected (0.10 sec)mysql> show databases;

+--------------------+

| Database |

+--------------------+|

information_schema |

| MA |

| mysql |

| test |

+--------------------+4 rows in set (0.00 sec)

mysql> use MA;

Database changed

mysql> create table shuiguo(price int);

Query OK, 0 rows affected (0.02 sec)

mysql> show tables;

+--------------+|

Tables_in_MA

|+--------------+|

shuiguo

|+--------------+

1 row in set (0.00 sec)

（2）、编辑服务器HK63配置文件

[root@HK63 ~]# vim /etc/my.cnf

     添加以下标红配置

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=mysqllog  #启用二进制日志，默认存在/var/lib/mysql  下面
server-id=1       #本机数据库ID 标示。其中master_id必须为1到232之间的一个正整数值
binlog-do-db=MA   #可以被从服务器复制的库。二进制需要同步的数据库名

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

（3）、重启HK63数据库

[root@HK63 ~]# service mysqld restart

（4）、进入数据库授权从服务器可访问权限

[root@HK63 ~]# mysql -uroot -p123456

mysql> grant replication slave on *.* to backup@192.168.2.105 identified by "123456";Query OK, 0 rows affected (0.00 sec)

查看主数据状态信息

mysql> show master status;

+-----------------+----------+--------------+------------------+

|File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-----------------+----------+--------------+------------------+

|mysqllog.000001 |      258 | MA           |                   |

+-----------------+----------+--------------+------------------+

1 row in set (0.00 sec)

查看二进制文件存放位置：

[root@HK63 ~]# ls /var/lib/mysql/

ibdata1      ib_logfile1  mysql            mysqllog.index  testib_logfile0 

MA           mysqllog.000001  mysql.sock

2、配置从服务器数据库服务前保证相关数据库一致性：

[root@HK63 ~]# mysqldump -u root -p123456 -A >all.sql

-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

[root@HK63 ~]# lsall.sql anaconda-ks.cfg install.log install.log.syslog

[root@HK63 ~]# scp all.sql 192.168.2.105:/root/

The authenticity of host '192.168.2.105 (192.168.2.105)' can't be established.RSA key fingerprint is a3:16:f7:51:22:be:d2:52:91:eb:b6:51:95:e6:b5:39.Are you sure you want to continue connecting (yes/no)? yr^HePlease type 'yes' or 'no': yes

Warning: Permanently added '192.168.2.105'

(RSA) to the list of known hosts.root@192.168.2.105's

password:

all.sql                                              100%  516KB 515.7KB/s   00:00

[root@HK64 ~]#  mysql -u root -p < all.sql

[root@HK64 ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 9Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| MA                 |

| mysql              |

| test               |

+--------------------+

4 rows in set (0.00 sec)

3、HK64从服务器相关配置

（1）、测试连接主服务数据库是否成功

[root@HK64 ~]# mysql -u backup -h 192.168.2.104 -p123456

Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

查看当前登入用户

mysql> select user();

+----------------------+

| user()               |

+----------------------+

| backup@192.168.2.105 |

+----------------------+

1 row in set (0.00 sec)

查看数据库，看不到MA，因为授权用户只运行复制读取MA权限没有查看权限

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| test               |

+--------------------+

2 rows in set (0.00 sec)

（2）设置从服务器mysql配置

[root@HK64 ~]# vim /etc/my.cnf

    添加以下标红参数

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0

#在配置文件中写入以下内容

server-id=2  #从服务器ID号，不要和主ID相同 ，如果设置多个从服务器，每个从服务器必须有一个唯一的server-id值，必须与主服务器的以及其它从服务器的不相同。可以认为server-id值类似>于IP地址：这些ID值能唯一识别复制服务器群集中的每个服务器实例。

master-host=192.168.2.104    #指定主服务器IP地址

master-user=backup           #制定在主服务器上可以进行同步的用户名

master-password=123456       #密码#####以下可以不写

#master-port = 3306   #同步所用的端口

#master-connect-retry=60   #断点重新连接时间

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

（3）、重启数据库服务

[root@HK64 ~]# service mysqld restart

（4）、查看从服务器状态

[root@HK64 ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.1.73 Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show slave status\G;

*************************** 1. row ***************************              

Slave_IO_State: Waiting for master to send event                 

Master_Host: 192.168.2.104                 

Master_User: backup                 

Master_Port: 3306               

Connect_Retry: 60             

Master_Log_File: mysqllog.000001         

Read_Master_Log_Pos: 258              

Relay_Log_File: mysqld-relay-bin.000002               

Relay_Log_Pos: 402       

Relay_Master_Log_File: mysqllog.000001            

Slave_IO_Running: Yes 可以看到这两个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: 258             

Relay_Log_Space: 558             

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: 1 row in set (0.00 sec)

Slave_IO_Running ：一个负责与主机的io通信

Slave_SQL_Running：负责自己的slave mysql进程

4、测试：

（1）登入主服务器HK63测试插入数据

[root@HK63 ~]# mysql -uroot -p123456
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql> 
mysql> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| MA                 |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> use MA
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------+
| Tables_in_MA |
+--------------+
| shuiguo      |
+--------------+
1 row in set (0.00 sec)

mysql> insert into  shuiguo values(10);
Query OK, 1 row affected (0.00 sec)

登入从服务器HK64查看是否同步成功

[root@HK64 ~]# mysql -uroot -p123456

mysql> use MA;

Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from shuiguo;

+-------+

| price |

+-------+

| 10 |

+-------+

1 row in set (0.00 sec)

（2）从服务器插入数据是否主服务器数据会同步，进行测试：

mysql> insert into shuiguo values(20);

Query OK, 1 row affected (0.00 sec)

mysql> select * from shuiguo;

+-------+

| price |

+-------+

| 10 |

| 20 |

+-------+

2 rows in set (0.00 sec)

[root@HK63 ~]# mysql -uroot -p123456

mysql> use MA

Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;+--------------+

| Tables_in_MA |

+--------------+

| shuiguo      |

+--------------+

1 row in set (0.00 sec)

mysql> select * from shuiguo ;

+-------+

| price |

+-------+

|    10 |

+-------+

1 row in set (0.00 sec)