MYSQL 主从数据库架构

1. 实验环境
DBServerMaster: 192.168.1.10
DBServerSlave:  192.168.1.20
相同的MYSQL版本两份，分别安装在上述的服务器。

2. 安装mysql
[root@localhost /]# yum install mysql mysql-server


3. 配置主(master)服务器

//先锁表备份数据。
mysql> flush tables with read lock;
mysql> mysqldump -hhostname -uusername -ppassword –all-databases > allbackupfile.sql; 
mysql> unlock tables;



//配置主服务器
[root@localhost /]# vi /etc/my.cnf


//在配置项mysqld加入两行代码
[mysqld]
log-bin=master-log-bin    //日志文件
server-id=1               //主服务器标识ID


//授权从服务器同步用户
[root@localhost /]# grant privileges repliaction slave on *.*  to 'slave'@'192.168.1.20' identified by '123456';  


//重新启动数据库
[root@localhost /]# service mysqld restart;


//查看数据库状态
mysql>show master status;
+-----------------------+----------+--------------+------------------+
| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------------+----------+--------------+------------------+
| master-log-bin.000001 |      875 |              |                  |
+-----------------------+----------+--------------+------------------+


4. 配置从(slave)数据库

//先锁表备份数据。
mysql> flush tables with read lock;
mysql> mysqldump -hhostname -uusername -ppassword –all-databases | gzip > backupfile.sql.gz; //压缩备份
mysql> unlock tables;



//配置从服务器
[root@localhost /]# vi /etc/my.cnf

//在配置项mysqld加入两行代码
[mysqld]
server-id=2               //从服务器标识ID 。和主服务器server-id=1不同。
master-host=192.168.1.10  //主服务器IP
master-user=slave         //主服务器授权给从服务器的用户
master-password=123456    //主服务器授权给从服务器的密码

设置slave参数
mysql>change master to master_host='192.168.1.10',master_user='slave',master_password='123456';

//启动从数据库
mysql>slave start;

//重新启动数据库
[root@localhost /]# service mysqld restart;

//查看数据库主要状态
mysql>show slave status;
Master_Host:192.168.1.10
Master_User:slave
Master_Port:3306
Master_Log_File:master-log-bin.000001
Read_Master_Log_Pos:875
Slave_IO_Running:Yes
Slave_SQL_Running:Yes



5. 测试库测试

5.1 主库测试

mysql> use test;

Database changed



mysql> create table master_slave_test(name varchar(100) not null,vkey int(11) not null);

mysql> show tables;

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

| Tables_in_test    |

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

| master_slave_test |

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



mysql> insert into master_slave_test set name='测试主从同步',vkey='1';

mysql> select * from master_slave_test;

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

| name               | vkey |

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

| 测试主从同步    |    1 |

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






5.2 从库测试

mysql> use test;
Database changed

mysql> show tables;

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

| Tables_in_test    |

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

| master_slave_test |

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

1 row in set (0.00 sec)



mysql> select * from master_slave_test;

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

| name               | vkey |

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

| 测试主从同步     |    1 |

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

1 row in set (0.00 sec)



通过上述测试，同步成功。