> 数据库 > MySQL >

Mysql主从方案的实现

时间:2014-12-29 19:13  来源:linux.it.net.cn   作者:IT

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

点击(此处)折叠或打开

  1. [client]
  2.  
  3. port = 3306
  4.  
  5. socket = /tmp/mysql.sock
  6.  
  7. [mysqld]
  8.  
  9. port = 3306
  10.  
  11. socket = /tmp/mysql.sock
  12.  
  13. basedir=/usr/local/mysql
  14.  
  15. datadir=/home/mysqldata
  16.  
  17. log-slow-queries=slow_query.txt
  18.  
  19. log-bin=mysql-bin197
  20.  
  21. long_query_time=2
  22.  
  23. skip-locking
  24.  
  25. skip-name-resolve
  26.  
  27. skip-innodb
  28.  
  29. bind-address=192.168.10.197
  30.  
  31. max_allowed_packet = 256M
  32.  
  33. query_cache_size=256M
  34.  
  35. max_connections=2000
  36.  
  37. max_connect_errors=10000
  38.  
  39. key_buffer_size=6000M
  40.  
  41. read_buffer_size=32M
  42.  
  43. read_rnd_buffer_size = 32M
  44.  
  45. myisam_sort_buffer_size=256M
  46.  
  47. tmp_table_size=512M
  48.  
  49. old-passwords
  50.  
  51. interactive_timeout=60
  52.  
  53. wait_timeout=60
  54.  
  55. connect_timeout=120
  56.  
  57. table_cache=8192
  58.  
  59. thread_cache_size=256
  60.  
  61. sort_buffer_size=64M
  62.  
  63. back_log = 500
  64.  
  65. thread_concurrency=32
  66.  
  67. server-id=1
  68.  
  69. log-bin=mysql-bin240
  70.  
  71. binlog-do-db=phpcmsv9
  72.  
  73. binlog-ignore-db=mysql
  74.  
  75. expire_logs_days=10
  76.  
  77. [mysqldump]
  78.  
  79. quick
  80.  
  81. max_allowed_packet = 1024M
  82.  
  83. [mysql]
  84.  
  85. no-auto-rehash
  86.  
  87. [isamchk]
  88.  
  89. key_buffer = 1024M
  90.  
  91. sort_buffer_size = 32M
  92.  
  93. read_buffer = 2M
  94.  
  95. write_buffer = 2M
  96.  
  97. [myisamchk]
  98.  
  99. key_buffer = 1024M
  100.  
  101. sort_buffer_size = 32M
  102.  
  103. read_buffer = 2M
  104.  
  105. write_buffer = 2M
  106.  
  107. [mysqlhotcopy]
  108.  
  109. interactive-timeout

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

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

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

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

点击(此处)折叠或打开

  1. mysql> show master status; #检查是否以master形式启动了。
  2.  
  3. +---------------------+----------+--------------+------------------+
  4.  
  5. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  6.  
  7. +---------------------+----------+--------------+------------------+
  8.  
  9. | mysql-bin240.000001 | 2342775 | phpcmsv9 | mysql |
  10.  
  11. +---------------------+----------+--------------+------------------+
  12.  
  13. 1 row in set (0.00 sec)
  14.  
  15. 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.  
  3. port = 3306
  4.  
  5. socket = /tmp/mysql.sock
  6.  
  7. [mysqld]
  8.  
  9. port = 3306
  10.  
  11. socket = /tmp/mysql.sock
  12.  
  13. basedir=/usr/local/mysql
  14.  
  15. datadir=/home/mysqldata
  16.  
  17. log-slow-queries=slow_query.txt
  18.  
  19. log-bin=mysql-bin198
  20.  
  21. long_query_time=2
  22.  
  23. skip-locking
  24.  
  25. bind-address=192.168.10.198
  26.  
  27. skip-name-resolve
  28.  
  29. skip-innodb
  30.  
  31. max_allowed_packet = 256M
  32.  
  33. query_cache_size=256M
  34.  
  35. max_connections=2000
  36.  
  37. max_connect_errors=10000
  38.  
  39. key_buffer_size=6000M
  40.  
  41. read_buffer_size=32M
  42.  
  43. read_rnd_buffer_size = 32M
  44.  
  45. myisam_sort_buffer_size=256M
  46.  
  47. tmp_table_size=512M
  48.  
  49. old-passwords
  50.  
  51. interactive_timeout=60
  52.  
  53. wait_timeout=60
  54.  
  55. connect_timeout=120
  56.  
  57. table_cache=8192
  58.  
  59. thread_cache_size=256
  60.  
  61. sort_buffer_size=64M
  62.  
  63. back_log = 500
  64.  
  65. thread_concurrency=8
  66.  
  67. server-id=2
  68.  
  69. master-host=192.168.10.197
  70.  
  71. master-user=backup
  72.  
  73. master-password=123
  74.  
  75. master-port=3306
  76.  
  77. replicate-do-db=phpcmsv9
  78.  
  79. replicate-ignore-db=mysql
  80.  
  81. master-connect-retry=60
  82.  
  83. expire_logs_days=10
  84.  
  85. [mysqldump]
  86.  
  87. quick
  88.  
  89. max_allowed_packet = 1024M
  90.  
  91. [mysql]
  92.  
  93. no-auto-rehash
  94.  
  95. [isamchk]
  96.  
  97. key_buffer = 1024M
  98.  
  99. sort_buffer_size = 32M
  100.  
  101. read_buffer = 2M
  102.  
  103. write_buffer = 2M
  104.  
  105. [myisamchk]
  106.  
  107. key_buffer = 1024M
  108.  
  109. sort_buffer_size = 32M
  110.  
  111. read_buffer = 2M
  112.  
  113. write_buffer = 2M
  114.  
  115. [mysqlhotcopy]
  116.  
  117. interactive-timeout

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

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

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

如果出现主从复制报错了

 

点击(此处)折叠或打开

  1. mysql> show slave status \G;
  2.  
  3. *************************** 1. row ***************************
  4.  
  5. Slave_IO_State: Connecting to master
  6.  
  7. Master_Host: 192.168.10.197
  8.  
  9. Master_User: backup
  10.  
  11. Master_Port: 3306
  12.  
  13. Connect_Retry: 60
  14.  
  15. Master_Log_File: mysql-bin240.000001
  16.  
  17. Read_Master_Log_Pos: 2342431
  18.  
  19. Relay_Log_File: localhost-relay-bin.000001
  20.  
  21. Relay_Log_Pos: 4
  22.  
  23. Relay_Master_Log_File: mysql-bin240.000001
  24.  
  25. Slave_IO_Running: No
  26.  
  27. Slave_SQL_Running: Yes
  28.  
  29. Replicate_Do_DB: phpcmsv9
  30.  
  31. Replicate_Ignore_DB: mysql
  32.  
  33. Replicate_Do_Table: 
  34.  
  35. Replicate_Ignore_Table: 
  36.  
  37. Replicate_Wild_Do_Table: 
  38.  
  39. Replicate_Wild_Ignore_Table: 
  40.  
  41. Last_Errno: 0
  42.  
  43. Last_Error: 
  44.  
  45. Skip_Counter: 0
  46.  
  47. Exec_Master_Log_Pos: 2342431
  48.  
  49. Relay_Log_Space: 106
  50.  
  51. Until_Condition: None
  52.  
  53. Until_Log_File: 
  54.  
  55. Until_Log_Pos: 0
  56.  
  57. Master_SSL_Allowed: No
  58.  
  59. Master_SSL_CA_File: 
  60.  
  61. Master_SSL_CA_Path: 
  62.  
  63. Master_SSL_Cert: 
  64.  
  65. Master_SSL_Cipher: 
  66.  
  67. Master_SSL_Key: 
  68.  
  69. Seconds_Behind_Master: NULL
  70.  
  71. Master_SSL_Verify_Server_Cert: No
  72.  
  73. Last_IO_Errno: 1130
  74.  
  75. Last_IO_Error: error connecting to master 'backup@192.168.10.197:3306' - retry-time: 60 retries: 86400
  76.  
  77. Last_SQL_Errno: 0
  78.  
  79. Last_SQL_Error: 
  80.  
  81. 1 row in set (0.00 sec)
  82.  
  83. ERROR: 
  84.  
  85. 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.  
  3. *************************** 1. row ***************************
  4.  
  5. Slave_IO_State: Waiting for master to send event
  6.  
  7. Master_Host: 192.168.10.197
  8.  
  9. Master_User: backup
  10.  
  11. Master_Port: 3306
  12.  
  13. Connect_Retry: 60
  14.  
  15. Master_Log_File: mysql-bin240.000001
  16.  
  17. Read_Master_Log_Pos: 2342775
  18.  
  19. Relay_Log_File: localhost-relay-bin.000002
  20.  
  21. Relay_Log_Pos: 598
  22.  
  23. Relay_Master_Log_File: mysql-bin240.000001
  24.  
  25. Slave_IO_Running: Yes
  26.  
  27. Slave_SQL_Running: Yes
  28.  
  29. Replicate_Do_DB: phpcmsv9
  30.  
  31. Replicate_Ignore_DB: mysql
  32.  
  33. Replicate_Do_Table: 
  34.  
  35. Replicate_Ignore_Table: 
  36.  
  37. Replicate_Wild_Do_Table: 
  38.  
  39. Replicate_Wild_Ignore_Table: 
  40.  
  41. Last_Errno: 0
  42.  
  43. Last_Error: 
  44.  
  45. Skip_Counter: 0
  46.  
  47. Exec_Master_Log_Pos: 2342775
  48.  
  49. Relay_Log_Space: 757
  50.  
  51. Until_Condition: None
  52.  
  53. Until_Log_File: 
  54.  
  55. Until_Log_Pos: 0
  56.  
  57. Master_SSL_Allowed: No
  58.  
  59. Master_SSL_CA_File: 
  60.  
  61. Master_SSL_CA_Path: 
  62.  
  63. Master_SSL_Cert: 
  64.  
  65. Master_SSL_Cipher: 
  66.  
  67. Master_SSL_Key: 
  68.  
  69. Seconds_Behind_Master: 0
  70.  
  71. Master_SSL_Verify_Server_Cert: No
  72.  
  73. Last_IO_Errno: 0
  74.  
  75. Last_IO_Error: 
  76.  
  77. Last_SQL_Errno: 0
  78.  
  79. Last_SQL_Error: 
  80.  
  81. 1 row in set (0.00 sec)
  82.  
  83. ERROR: 
  84.  
  85. No query specified

 

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

(责任编辑：IT)
    最近关注
    热点内容