数据库的主从,一般主库做增改删操作,从库做查询,这就是传说中的读写分离。
缺点就是主要是挂了,从也没有数据了。
数据库的主主,就是互相复制,各自为主,各自又为对方的从。
缺点就是各种复制,影响性能。
1准备:
准备两个虚拟机,每个虚拟机里装一个mysql,我的版本是5.1.7
2主数据库配置:
#service mysqld stop#vim /etc/my.cnf
在[mysqld]下添加
log-bin=mysql-bin-master #打开日志,日志名称为
mysql-bin-master,这个名字随便取
server-id=1 #唯一id,id号随便来,不要跟另一个主数据库重复就行
replicate-do-db=dnf #要同步的数据库(我刚刚偷偷建了个叫dnf的数据库)
binlog-ignore-db=mysql #不许同步的数据库
#service mysqld restart
另一个主数据库做同样的操作
3互相授权
mysql>grant replication slave on *.* to slave@192.168.198.128 identified by 'slave';
mysql>grant replication slave on *.* to slave@192.168.198.130 identified by 'slave';
创建slave账号,密码为slave,并赋予replication slave权限(我猜的)
4告诉对方自己bin-log的信息
主A(192.168.198.128):mysql>show master status;
+------------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
| mysql-bin-slave.000001 | 275 | | mysql |
+------------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
| mysql-bin-slave.000001 | 275 | | mysql |
+------------------------+----------+--------------+------------------+
主B(192.168.198.130):
mysql>slave stop
mysql>change master to master_host='192.168.198.128',master_user='slave',master_password='slave', master_log_file='
mysql-bin-slave.000001', master_log_pos=275;
小提示:这里的
master_log_file对应的file,也就是这里的
mysql-bin-slave.000001,这里的
master_log_pos对应的
Position,也就是这里的275
5然后两个库反过来做相应操作,操作完之后,执行
mysql>slave start
mysql>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.198.128
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-master.000002
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 258
Relay_Master_Log_File: mysql-bin-master.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: dnf
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: 106
Relay_Log_Space: 414
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_State: Waiting for master to send event
Master_Host: 192.168.198.128
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-master.000002
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 258
Relay_Master_Log_File: mysql-bin-master.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: dnf
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: 106
Relay_Log_Space: 414
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)
ERROR:
No query specified
No query specified
如果出现Slave_IO_Running: Yes
Slave_SQL_Running: Yes两个都是yes,那么你就成功了,试试吧