日期:2014-05-16  浏览次数:20836 次

MySQL主从服务器搭建

mysql的增删改查 都会了,有个朋友向我请教数据库的主从备份上的问题,没有搭建过,今天正好有时间,就搭建了下,把其中搭建过程中遇到的问题,总结了下,和大家分享下:
mysql数据库的安装 就不在这赘述了 ,首先要有两个mysql服务器:


系统和数据库版本是:centos5.5 mysql 5.0.45

masterIP 192.168.11.20
slaveIP 192.168.11.2

master主机上有以下操作:

修改/etc/my.cnf文件

# The MySQL server
[mysqld]
server-id=1???//主机标识,整数
log-bin=mysql-bin??//确保此文件可写
binlog-do-db=beifen??//要备份的数据,可以写多个
binlog-do-db=cong
binlog-ignore-db=bubeifen?//不需要备份的,也可以写多个

//创建同步用户
mysql> grant replication slave,reload,super on *.* to 'cong'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

[root@localhost ~]# mysql -u cong -p123456 -h 192.168.11.20
Welcome to the MySQL monitor.? Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.45-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select user();
+--------------------+
| user()???????????? |
+--------------------+
| cong@192.168.11.20 |
+--------------------+
1 row in set (0.00 sec)

//slave主机上的操作:

# The MySQL server
[mysqld]
server-id=2
master-host=192.168.11.20??//master主机IP
master-user=cong???//同步用户
master-password=123456???//同步密码

然后重启master和slave上的mysql数据库

//在master上

mysql> show master status \G
*************************** 1. row ***************************
??????????? File: mysql-bin.000008
??????? Position: 98
??? Binlog_Do_DB: beifen,cong
Binlog_Ignore_DB: bubeifen
1 row in set (0.04 sec)

//在slave上查看

mysql> show slave status \G
*************************** 1. row ***************************
???????????? Slave_IO_State: Waiting for master to send event
??????????????? Master_Host: 192.168.11.20
??????????????? Master_User: cong
??????????????? Master_Port: 3306
????????????? Connect_Retry: 60
??????????? Master_Log_File: mysql-bin.000008
??????? Read_Master_Log_Pos: 98
???????????? Relay_Log_File: localhost-relay-bin.000002
????????????? Relay_Log_Pos: 235
????? Relay_Master_Log_File: mysql-bin.000008
?????????? 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: 98
??????????? Relay_Log_Space: 235
??????????? 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)

mysql> load data from master;
Query OK, 0 rows affected, 2 warnings (10.10 sec)

mysql> show databases;
+--------------------+
| Database?????????? |
+--------------------+
| information_schema |
| cong?????????????? |
| beifen???????????? |
| mysql????????????? |
| test?????????????? |
+--------------------+
8 rows in set (0.00 sec)

发现数据库cong,beifei这两个数据库 都同步过来了。


mysql> show processlist \G
*************************** 1. row ***************************
???? Id: 12
?? User: system user
?? Host:
???? db: NULL
Command: Connect
?? Time: 1742
? State: Waiting for master to send event
?? Info: NULL
*************************** 2. row ***************************
???? Id: 13
?? User: system user
?? Host:
???? db: NULL
Command: Connect
?? Time: 1729
? State: Has read all relay log; waiting for the slave I/O thread to update it
?? Info: NULL
*************************** 3.