Python mysql 主备 mysql主备方案

转载

码海航行侠 2023-10-18 17:54:35

文章标签 Python mysql 主备 mysql SSL SQL 文章分类 Python 后端开发

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建立用户

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输入

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查询，发现后面插入的两条语句已经同步过来了，随后继续插入测试没有发现问题。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。