How to Install MariaDB Galera Cluster 5.5 on Debia

程序员文章站 2022-04-09 14:33:57

...

为什么是MariaDB，为什么是Galera，为什么是Debian，这些闲话都不说了，直入正题。本文参考资料：AskMonty KB。 1. 在两台机器上分别安装MariaDB Galera Cluster，可以使用两个虚拟机，互相之间IP可以ping通即可。由于apt-get是如此好用，因此请保证你的De

为什么是MariaDB，为什么是Galera，为什么是Debian，这些闲话都不说了，直入正题。

本文参考资料：AskMonty KB。

1. 在两台机器上分别安装MariaDB Galera Cluster，可以使用两个虚拟机，互相之间IP可以ping通即可。
由于apt-get是如此好用，因此请保证你的Debian可以联网，然后执行：

# apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
# cd /etc/apt/sources.list.d/
--创建MariaDB.list文件
# vi MariaDB.list
--添加如下内容
# MariaDB 5.5 repository list - created 2013-04-25 09:13 UTC
# http://mariadb.org/mariadb/repositories/
deb http://mirrors.fe.up.pt/pub/mariadb/repo/5.5/debian squeeze main
deb-src http://mirrors.fe.up.pt/pub/mariadb/repo/5.5/debian squeeze main
--更新apt资源
# apt-get update
# apt-cache search mariadb
libmariadbclient-dev - MariaDB database development files
libmariadbclient18 - MariaDB database client library
libmariadbd-dev - MariaDB embedded database development files
libmysqlclient18 - Virtual package to satisfy external depends
mariadb-client - MariaDB database client (metapackage depending on the latest version)
mariadb-client-5.5 - MariaDB database client binaries
mariadb-client-core-5.5 - MariaDB database core client binaries
mariadb-common - MariaDB database common files (e.g. /etc/mysql/conf.d/mariadb.cnf)
mariadb-galera-server - MariaDB database server with Galera cluster
mariadb-galera-server-5.5 - MariaDB database server with Galera cluster binaries
mariadb-server - MariaDB database server (metapackage depending on the latest version)
mariadb-server-5.5 - MariaDB database server binaries
mariadb-server-core-5.5 - MariaDB database core server files
mariadb-test - MariaDB database regression test suite (metapackage depending on the latest version)
mariadb-test-5.5 - MariaDB database regression test suite
mysql-common - MariaDB database common files (e.g. /etc/mysql/my.cnf)
--只需要安装mariadb-galera-server即可，注意，会自动卸载Debian中预装的MySQL 5.1
# apt-get install mariadb-galera-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
  galera libaio1 libmariadbclient18 libmysqlclient18 mariadb-client-5.5 mariadb-client-core-5.5 mariadb-common mariadb-galera-server-5.5
Suggested packages:
  tinyca mariadb-test
The following packages will be REMOVED:
  mysql-client-5.1 mysql-server mysql-server-5.1 mysql-server-core-5.1
The following NEW packages will be installed:
  galera libaio1 libmariadbclient18 libmysqlclient18 mariadb-client-5.5 mariadb-client-core-5.5 mariadb-common mariadb-galera-server mariadb-galera-server-5.5
0 upgraded, 9 newly installed, 4 to remove and 46 not upgraded.
Need to get 32.3 MB of archives.
After this operation, 55.2 MB of additional disk space will be used.
Do you want to continue [Y/n]?

2. 修改my.cnf文件，修改或者添加如下行，在两个节点中都修改。

# vi /etc/mysql/my.cnf
wsrep_cluster_name = my_test_cluster
wsrep_node_name = debian
wsrep_provider = /usr/lib/galera/libgalera_smm.so
wsrep_sst_method = mysqldump
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
--注意，默认配置MariiaDB只监听localhost，因此需要修改bind-address参数
第一个节点：bind-address		= 192.168.1.108
第二个节点：bind-address		= 192.168.1.109

3. 启动第一个节点

--如果之前数据库已经启动，可以先关闭
# /etc/init.d/mysql stop
--启动MariaDB Galera Cluster
# /usr/bin/mysqld_safe --wsrep_cluster_address=gcomm:// > /dev/null 2>&1 &

4. 启动第二个节点

--其中192.168.1.108是第一个节点MariaDB的监听IP
# /usr/bin/mysqld_safe --wsrep_cluster_address=gcomm://192.168.1.108 > /dev/null 2>&1 &

5. 检查整个Cluster状态

--随便登入任何一台机器的MariaDB
# mysql 
MariaDB [(none)]> show status like 'wsrep%';
+----------------------------+---------------------------------------+
| Variable_name              | Value                                 |
+----------------------------+---------------------------------------+
| wsrep_local_state_uuid     | 5947fd92-adb9-11e2-0800-8d35ba835f31  |
| wsrep_protocol_version     | 4                                     |
| wsrep_last_committed       | 9                                     |
| wsrep_replicated           | 0                                     |
| wsrep_replicated_bytes     | 0                                     |
| wsrep_received             | 2                                     |
| wsrep_received_bytes       | 198                                   |
| wsrep_local_commits        | 0                                     |
| wsrep_local_cert_failures  | 0                                     |
| wsrep_local_bf_aborts      | 0                                     |
| wsrep_local_replays        | 0                                     |
| wsrep_local_send_queue     | 0                                     |
| wsrep_local_send_queue_avg | 0.000000                              |
| wsrep_local_recv_queue     | 0                                     |
| wsrep_local_recv_queue_avg | 0.000000                              |
| wsrep_flow_control_paused  | 0.000000                              |
| wsrep_flow_control_sent    | 0                                     |
| wsrep_flow_control_recv    | 0                                     |
| wsrep_cert_deps_distance   | 0.000000                              |
| wsrep_apply_oooe           | 0.000000                              |
| wsrep_apply_oool           | 0.000000                              |
| wsrep_apply_window         | 0.000000                              |
| wsrep_commit_oooe          | 0.000000                              |
| wsrep_commit_oool          | 0.000000                              |
| wsrep_commit_window        | 0.000000                              |
| wsrep_local_state          | 4                                     |
| wsrep_local_state_comment  | Synced                                |
| wsrep_cert_index_size      | 0                                     |
| wsrep_causal_reads         | 0                                     |
| wsrep_incoming_addresses   | 192.168.1.108:3306,192.168.1.109:3306 |
| wsrep_cluster_conf_id      | 6                                     |
| wsrep_cluster_size         | 2                                     |
| wsrep_cluster_state_uuid   | 5947fd92-adb9-11e2-0800-8d35ba835f31  |
| wsrep_cluster_status       | Primary                               |
| wsrep_connected            | ON                                    |
| wsrep_local_index          | 0                                     |
| wsrep_provider_name        | Galera                                |
| wsrep_provider_vendor      | Codership Oy      |
| wsrep_provider_version     | 23.2.4(r147)                          |
| wsrep_ready                | ON                                    |
+----------------------------+---------------------------------------+
40 rows in set (0.00 sec)

6. 测试双活读写

--任选一个节点创建数据库，创建表，插入数据
MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS my_db default charset utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.01 sec)
?
MariaDB [(none)]> connect my_db
Connection id:    8
Current database: my_db
?
MariaDB [my_db]> create table my_table (name varchar(20) not null default '') default charset utf8;
Query OK, 0 rows affected (0.00 sec)
?
MariaDB [my_db]> insert into my_table values('enmotech');
Query OK, 1 row affected (0.00 sec)
?
--在另一个节点查询并更新
MariaDB [my_db]> select * from my_table;
+----------+
| name     |
+----------+
| enmotech |
+----------+
1 row in set (0.00 sec)
?
MariaDB [my_db]> update my_table set name='enmotech_mysql' where name='enmotech';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
?
--在之前的节点查询
MariaDB [my_db]> select * from my_table;
+----------------+
| name           |
+----------------+
| enmotech_mysql |
+----------------+
1 row in set (0.00 sec)

本文仅仅是最简单的安装及功能测试，不论健壮性、高效性、容错性如何，至少这种双活数据库的配置比起Oracle的任何一种解决方案都简单。

How to Install MariaDB Galera Cluster 5.5 on Debia Share/Save