Chinaunix首页 | 论坛 | 博客
  • 博客访问: 6370726
  • 博文数量: 162
  • 博客积分: 3600
  • 博客等级: 中校
  • 技术积分: 10366
  • 用 户 组: 普通用户
  • 注册时间: 2011-01-19 11:23
个人简介

专注系统运维、网络架构,研究技术解决方案,记录我的思想轨迹、工作学习、生活和关注的领域

文章分类
文章存档

2014年(2)

2013年(16)

2012年(27)

2011年(117)

分类: Mysql/postgreSQL

2012-06-12 09:45:15

Mysql主从方案介绍

mysql主从方案主要作用:

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

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

热备,slavemaster的数据准实时同步。

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

系统环境:

OSRHEL5.4

主:192.168.10.197

从:192.168.10.198

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

2、my.cnf配置

a、配置MASTER

点击(此处)折叠或打开

  1. [client]

  2. port = 3306

  3. socket = /tmp/mysql.sock

  4. [mysqld]

  5. port = 3306

  6. socket = /tmp/mysql.sock

  7. basedir=/usr/local/mysql

  8. datadir=/home/mysqldata

  9. log-slow-queries=slow_query.txt

  10. log-bin=mysql-bin197

  11. long_query_time=2

  12. skip-locking

  13. skip-name-resolve

  14. skip-innodb

  15. bind-address=192.168.10.197

  16. max_allowed_packet = 256M

  17. query_cache_size=256M

  18. max_connections=2000

  19. max_connect_errors=10000

  20. key_buffer_size=6000M

  21. read_buffer_size=32M

  22. read_rnd_buffer_size = 32M

  23. myisam_sort_buffer_size=256M

  24. tmp_table_size=512M

  25. old-passwords

  26. interactive_timeout=60

  27. wait_timeout=60

  28. connect_timeout=120

  29. table_cache=8192

  30. thread_cache_size=256

  31. sort_buffer_size=64M

  32. back_log = 500

  33. thread_concurrency=32

  34. server-id=1

  35. log-bin=mysql-bin240

  36. binlog-do-db=phpcmsv9

  37. binlog-ignore-db=mysql

  38. expire_logs_days=10

  39. [mysqldump]

  40. quick

  41. max_allowed_packet = 1024M

  42. [mysql]

  43. no-auto-rehash

  44. [isamchk]

  45. key_buffer = 1024M

  46. sort_buffer_size = 32M

  47. read_buffer = 2M

  48. write_buffer = 2M

  49. [myisamchk]

  50. key_buffer = 1024M

  51. sort_buffer_size = 32M

  52. read_buffer = 2M

  53. write_buffer = 2M

  54. [mysqlhotcopy]

  55. interactive-timeout

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

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

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

重启master数据库,运行检查:

点击(此处)折叠或打开

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

  2. +---------------------+----------+--------------+------------------+

  3. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

  4. +---------------------+----------+--------------+------------------+

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

  6. +---------------------+----------+--------------+------------------+

  7. 1 row in set (0.00 sec)

  8. mysql> show variables like "%log%";

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

master上为slave建立用户

点击(此处)折叠或打开

  1. mysql> grant replication slave, reload, super on *.* to 'backup'@'192.168.10.198' identified by '123';
这样,主机配置完毕。
b
slave配置

点击(此处)折叠或打开

  1. [client]

  2. port = 3306

  3. socket = /tmp/mysql.sock

  4. [mysqld]

  5. port = 3306

  6. socket = /tmp/mysql.sock

  7. basedir=/usr/local/mysql

  8. datadir=/home/mysqldata

  9. log-slow-queries=slow_query.txt

  10. log-bin=mysql-bin198

  11. long_query_time=2

  12. skip-locking

  13. bind-address=192.168.10.198

  14. skip-name-resolve

  15. skip-innodb

  16. max_allowed_packet = 256M

  17. query_cache_size=256M

  18. max_connections=2000

  19. max_connect_errors=10000

  20. key_buffer_size=6000M

  21. read_buffer_size=32M

  22. read_rnd_buffer_size = 32M

  23. myisam_sort_buffer_size=256M

  24. tmp_table_size=512M

  25. old-passwords

  26. interactive_timeout=60

  27. wait_timeout=60

  28. connect_timeout=120

  29. table_cache=8192

  30. thread_cache_size=256

  31. sort_buffer_size=64M

  32. back_log = 500

  33. thread_concurrency=8

  34. server-id=2

  35. master-host=192.168.10.197

  36. master-user=backup

  37. master-password=123

  38. master-port=3306

  39. replicate-do-db=phpcmsv9

  40. replicate-ignore-db=mysql

  41. master-connect-retry=60

  42. expire_logs_days=10

  43. [mysqldump]

  44. quick

  45. max_allowed_packet = 1024M

  46. [mysql]

  47. no-auto-rehash

  48. [isamchk]

  49. key_buffer = 1024M

  50. sort_buffer_size = 32M

  51. read_buffer = 2M

  52. write_buffer = 2M

  53. [myisamchk]

  54. key_buffer = 1024M

  55. sort_buffer_size = 32M

  56. read_buffer = 2M

  57. write_buffer = 2M

  58. [mysqlhotcopy]

  59. interactive-timeout

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

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

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

如果出现主从复制报错了

点击(此处)折叠或打开

  1. mysql> show slave status \G;

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

  3. Slave_IO_State: Connecting to master

  4. Master_Host: 192.168.10.197

  5. Master_User: backup

  6. Master_Port: 3306

  7. Connect_Retry: 60

  8. Master_Log_File: mysql-bin240.000001

  9. Read_Master_Log_Pos: 2342431

  10. Relay_Log_File: localhost-relay-bin.000001

  11. Relay_Log_Pos: 4

  12. Relay_Master_Log_File: mysql-bin240.000001

  13. Slave_IO_Running: No

  14. Slave_SQL_Running: Yes

  15. Replicate_Do_DB: phpcmsv9

  16. Replicate_Ignore_DB: mysql

  17. Replicate_Do_Table:

  18. Replicate_Ignore_Table:

  19. Replicate_Wild_Do_Table:

  20. Replicate_Wild_Ignore_Table:

  21. Last_Errno: 0

  22. Last_Error:

  23. Skip_Counter: 0

  24. Exec_Master_Log_Pos: 2342431

  25. Relay_Log_Space: 106

  26. Until_Condition: None

  27. Until_Log_File:

  28. Until_Log_Pos: 0

  29. Master_SSL_Allowed: No

  30. Master_SSL_CA_File:

  31. Master_SSL_CA_Path:

  32. Master_SSL_Cert:

  33. Master_SSL_Cipher:

  34. Master_SSL_Key:

  35. Seconds_Behind_Master: NULL

  36. Master_SSL_Verify_Server_Cert: No

  37. Last_IO_Errno: 1130

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

  39. Last_SQL_Errno: 0

  40. Last_SQL_Error:

  41. 1 row in set (0.00 sec)

  42. ERROR:

  43. No query specified

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

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

4输入

点击(此处)折叠或打开

  1. 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 命令后

点击(此处)折叠或打开

  1. mysql> show slave status \G;

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

  3. Slave_IO_State: Waiting for master to send event

  4. Master_Host: 192.168.10.197

  5. Master_User: backup

  6. Master_Port: 3306

  7. Connect_Retry: 60

  8. Master_Log_File: mysql-bin240.000001

  9. Read_Master_Log_Pos: 2342775

  10. Relay_Log_File: localhost-relay-bin.000002

  11. Relay_Log_Pos: 598

  12. Relay_Master_Log_File: mysql-bin240.000001

  13. Slave_IO_Running: Yes

  14. Slave_SQL_Running: Yes

  15. Replicate_Do_DB: phpcmsv9

  16. Replicate_Ignore_DB: mysql

  17. Replicate_Do_Table:

  18. Replicate_Ignore_Table:

  19. Replicate_Wild_Do_Table:

  20. Replicate_Wild_Ignore_Table:

  21. Last_Errno: 0

  22. Last_Error:

  23. Skip_Counter: 0

  24. Exec_Master_Log_Pos: 2342775

  25. Relay_Log_Space: 757

  26. Until_Condition: None

  27. Until_Log_File:

  28. Until_Log_Pos: 0

  29. Master_SSL_Allowed: No

  30. Master_SSL_CA_File:

  31. Master_SSL_CA_Path:

  32. Master_SSL_Cert:

  33. Master_SSL_Cipher:

  34. Master_SSL_Key:

  35. Seconds_Behind_Master: 0

  36. Master_SSL_Verify_Server_Cert: No

  37. Last_IO_Errno: 0

  38. Last_IO_Error:

  39. Last_SQL_Errno: 0

  40. Last_SQL_Error:

  41. 1 row in set (0.00 sec)

  42. ERROR:

  43. No query specified

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

阅读(32374) | 评论(0) | 转发(6) |
给主人留下些什么吧!~~