一、1、实验环境:两台服务器搭建主从服务实现同步
2、实验拓扑:
主服务器HK63(IP:192.168.2.104)---------从
服务器HK64(IP:192.168.2.105)
3、实验思路:
(1)、准备两台服务器
(2)、两台服务器搭建数据库(数据库版本5.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)
[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)