Mysql主从方案的实现

Mysql主从方案介绍

mysql主从方案主要作用：

读写分离，使数据库能支撑更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

发扬不同表引擎的优点。目前Myisam表的查询速度比innodb略快，而写入并发innodb比myIsam要好。那么，我们可以使用innodb作为master，处理高并发写入，使用master作为slave，接受查询。或在myisam slave中建立全文索引，解决innodb无全文索引的弱点。

热备，slave和master的数据“准实时”同步。

准备工作。先分别安装两台MYSQL。

系统环境：

OS：RHEL5.4

主：192.168.10.197

从：192.168.10.198

1、mysql 的安装这里就不介绍了，详见安装文档。

2、my.cnf配置

a、配置MASTER

点击(此处)折叠或打开

[client]

port            = 3306

socket          = /tmp/mysql.sock

[mysqld]

port            = 3306

socket          = /tmp/mysql.sock

basedir=/usr/local/mysql

datadir=/home/mysqldata

log-slow-queries=slow_query.txt

log-bin=mysql-bin197

long_query_time=2

skip-locking

skip-name-resolve

skip-innodb

bind-address=192.168.10.197

max_allowed_packet = 256M

query_cache_size=256M

max_connections=2000

max_connect_errors=10000

key_buffer_size=6000M

read_buffer_size=32M

read_rnd_buffer_size = 32M

myisam_sort_buffer_size=256M

tmp_table_size=512M

old-passwords

interactive_timeout=60

wait_timeout=60

connect_timeout=120

table_cache=8192

thread_cache_size=256

sort_buffer_size=64M

back_log = 500

thread_concurrency=32

server-id=1

log-bin=mysql-bin240

binlog-do-db=phpcmsv9

binlog-ignore-db=mysql

expire_logs_days=10

[mysqldump]

quick

max_allowed_packet = 1024M

[mysql]

no-auto-rehash

[isamchk]

key_buffer = 1024M

sort_buffer_size = 32M

read_buffer = 2M

write_buffer = 2M

[myisamchk]

key_buffer = 1024M

sort_buffer_size = 32M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

注释：红色是修改的部分。

其中，作为主机，server-id必须为1.

binlog_do_db为需要复制的db。 binlog_ignore_db为忽略复制的db。需要增加DB的话，就增加相应的一行。

重启master数据库，运行检查：

点击(此处)折叠或打开

mysql> show master status; #检查是否以master形式启动了。

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

| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin240.000001 |  2342775 | phpcmsv9     | mysql            |

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

1 row in set (0.00 sec)

mysql> show variables like "%log%";

#需要看到这样的一行，说明binlog已经开启了: log_bin | ON

在master上为slave建立用户

点击(此处)折叠或打开

mysql> grant replication slave, reload, super on *.* to 'backup'@'192.168.10.198' identified by '123';

这样，主机配置完毕。
b、slave配置

点击(此处)折叠或打开

[client]

port            = 3306

socket          = /tmp/mysql.sock

[mysqld]

port            = 3306

socket          = /tmp/mysql.sock

basedir=/usr/local/mysql

datadir=/home/mysqldata

log-slow-queries=slow_query.txt

log-bin=mysql-bin198

long_query_time=2

skip-locking

bind-address=192.168.10.198

skip-name-resolve

skip-innodb

max_allowed_packet = 256M

query_cache_size=256M

max_connections=2000

max_connect_errors=10000

key_buffer_size=6000M

read_buffer_size=32M

read_rnd_buffer_size = 32M

myisam_sort_buffer_size=256M

tmp_table_size=512M

old-passwords

interactive_timeout=60

wait_timeout=60

connect_timeout=120

table_cache=8192

thread_cache_size=256

sort_buffer_size=64M

back_log = 500

thread_concurrency=8

server-id=2

master-host=192.168.10.197

master-user=backup

master-password=123

master-port=3306

replicate-do-db=phpcmsv9

replicate-ignore-db=mysql

master-connect-retry=60

expire_logs_days=10

[mysqldump]

quick

max_allowed_packet = 1024M

[mysql]

no-auto-rehash

[isamchk]

key_buffer = 1024M

sort_buffer_size = 32M

read_buffer = 2M

write_buffer = 2M

[myisamchk]

key_buffer = 1024M

sort_buffer_size = 32M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

注释：红色部分为修改的地方

1.启动主服务器和从服务器服务，在从服务器192.168.17.2上使用命令slave start启动复制;

2.随便使用命令show slave status;

如果出现主从复制报错了

点击(此处)折叠或打开

mysql> show slave status \G;

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

Slave_IO_State: Connecting to master

Master_Host: 192.168.10.197

Master_User: backup

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin240.000001

Read_Master_Log_Pos: 2342431

Relay_Log_File: localhost-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin240.000001

Slave_IO_Running: No

Slave_SQL_Running: Yes

Replicate_Do_DB: phpcmsv9

Replicate_Ignore_DB: mysql

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: 2342431

Relay_Log_Space: 106

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: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 1130

Last_IO_Error: error connecting to master 'backup@192.168.10.197:3306' - retry-time: 60  retries: 86400

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set (0.00 sec)

ERROR:

No query specified

这是由于MASTER没有赋予权限的关系

3在从服务器192.168.17.2运行slave stop;停止复制命令

4输入

点击(此处)折叠或打开

mysql>CHANGE MASTER to MASTER_HOST='192.168.15.197', MASTER_PORT=3306, MASTER_USER='backup', MASTER_PASSWORD='123', MASTER_LOG_FILE=’mysql-bin240.000001'；, MASTER_LOG_POS=2342775;

5然后重新启动slave start 命令后

点击(此处)折叠或打开

mysql> show slave status \G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.10.197

Master_User: backup

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin240.000001

Read_Master_Log_Pos: 2342775

Relay_Log_File: localhost-relay-bin.000002

Relay_Log_Pos: 598

Relay_Master_Log_File: mysql-bin240.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: phpcmsv9

Replicate_Ignore_DB: mysql

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: 2342775

Relay_Log_Space: 757

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

在从库192.168.17.2 select查询，发现后面插入的两条语句已经同步过来了，随后继续插入测试没有发现问题。