9.3 rpm安裝mariadb · 王康寧的筆記

mariadb集群配置-centos7版本三臺mariadb集群 ------------------------------------------------------------------------------------------------------------------------- mariadb集群配置環境信息 MariaDB Server：MariaDB-10.2.10 CentOS：CentOS Linux release7.2.1511 (Core) MariaDB Galera Cluster 三個集群節點主機名和IP地址信息： 192.168.1.51 db1 192.168.1.52 db2 192.168.1.53 db3 環境準備，最小化安裝CentOS7.2后，安裝net-tools-2.0-0.17.20131004git.el7.x86_64.rpm和lrzsz-0.12.20-36.el7.x86_64.rpm，方便遠程管理和傳輸文件。 1. 編輯配置hosts文件 #vi /etc/hosts 127.0.0.1 localhost.localdomain localhost 192.168.1.51 db1 192.168.1.52 db2 192.168.1.53 db3 2. #vi /etc/security/limits.conf * soft nofile 65536 * hard nofile 65536 3. #vi /etc/sysctl.conf fs.file-max=655350 net.ipv4.ip_local_port_range = 1025 65000 net.ipv4.tcp_tw_recycle = 1 最后執行： sysctl -p 4. 關閉SELINUX和防火墻 vi /etc/selinux/config SELINUX=disabled #systemctl stop firewalld.service #systemctl disable firewalld.service #setenforce 0 部署MariaDB **從MariaDB 10.1版本開始，Galera Cluster就已經包含在MariaDB包里面了，不需要單獨部署MariaDB-Galera-server 和galera 包。** galera-25.3.20-1.rhel7.el7.centos.x86_64.rpm MariaDB-10.2.10-centos7-x86_64-compat.rpm MariaDB-10.2.10-centos7-x86_64-client.rpm MariaDB-10.2.10-centos7-x86_64-server.rpm MariaDB-10.2.10-centos7-x86_64-common.rpm 準備好這五個包步驟一：配置Yum源（192.168.1.51,192.168.1.52,192.168.1.53） #touch /etc/yum.repos.d/MariaDB-IDC.repo 添加如下內容： [mariadb] name = MariaDB baseurl =http://yum.mariadb.org/10.1/centos7-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1 上面的yum源可能太慢，可以考慮使用如下地址： [mariadb] name = MariaDB baseurl=https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-10.2.10/yum/centos/7.2/x86_64 也可以用本地源步驟二：安裝MariaDB（192.168.1.51,192.168.1.52,192.168.1.53） #yum install MariaDB-server -y 如果無法訪問外網，或者外網太慢，可以考慮單獨部署一個本地的Yum源用來安裝依賴包，MariaDB的包下載后直接yum安裝，這里演示使用YUM和本地文件相結合的方式部署MariaDB Galera Cluster。 #mount /dev/cdrom /media #cat /etc/yum.repos.d/local.repo [local] name=local baseurl=file:///media gpgcheck=0 enabled=1 #ls galera-25.3.20-1.rhel7.el7.centos.x86_64.rpm MariaDB-10.2.10-centos7-x86_64-compat.rpm MariaDB-10.2.10-centos7-x86_64-client.rpm MariaDB-10.2.10-centos7-x86_64-server.rpm MariaDB-10.2.10-centos7-x86_64-common.rpm #yum install MariaDB-10.2.10-centos7-x86_64-* galera-25.3.20-1.rhel7.el7.centos.x86_64.rpm 配置 MariaDB Galera Cluster 下面我們開始配置MariaDB Galera Cluster，分別修改MariaDB Galera集群的每個節點上的/etc/my.cnf.d/server.cnf文件，具體每個節點的內容如下： 1. 192.168.1.51節點的/etc/my.cnf.d/server.cnf文件內容： [root@db1 ~]# cat /etc/my.cnf.d/server.cnf | grep -v "#" | grep -v "^$" [server] [mysqld] server_id=151 datadir=/opt/galera user=mysql skip-external-locking skip-name-resolve character-set-server=utf8 [galera] wsrep_causal_reads=ON #節點應用完事務才返回查詢請求 wsrep_provider_options="gcache.size=4G"#同步復制緩沖池 wsrep_certify_nonPK=ON #為沒有顯式申明主鍵的表生成一個用于certificationtest的主鍵，默認為ON #log-bin=/opt/galera/mysql-bin #如果不接從庫，注釋掉 #log_slave_updates=1 #如果不接從庫，注釋掉 query_cache_size=0 #關閉查詢緩存 wsrep_on=ON #開啟全同步復制模式 wsrep_provider=/usr/lib64/galera/libgalera_smm.so#galera library wsrep_cluster_name=MariaDB-Galera-Cluster wsrep_cluster_address="gcomm://192.168.1.51,192.168.1.52,192.168.1.53" #galera cluster URL wsrep_node_name=db1 wsrep_node_address=192.168.1.51 binlog_format=row default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 #主鍵自增模式修改為交叉模式 wsrep_slave_threads=8 #開啟并行復制線程，根據CPU核數設置 innodb_flush_log_at_trx_commit=0 #事務提交每隔1秒刷盤 innodb_buffer_pool_size=2G wsrep_sst_method=rsync [embedded] [mariadb] [mariadb-10.1] 2. 192.168.1.52節點的/etc/my.cnf.d/server.cnf文件內容（每個接點除了server_id、wsrep_node_name和wsrep_node_address不同外，其它都相同，可以復制后修改這三處）： [root@db2 ~]# cat /etc/my.cnf.d/server.cnf | grep -v "#" | grep -v "^$" [server] [mysqld] server_id=152 datadir=/opt/galera user=mysql skip-external-locking skip-name-resolve character-set-server=utf8 [galera] wsrep_causal_reads=ON wsrep_provider_options="gcache.size=4G" wsrep_certify_nonPK=ON query_cache_size=0 wsrep_on=ON wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_name=MariaDB-Galera-Cluster wsrep_cluster_address="gcomm://192.168.1.51,192.168.1.52,192.168.1.53" wsrep_node_name=db2 wsrep_node_address=192.168.1.52 binlog_format=row default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 wsrep_slave_threads=8 innodb_flush_log_at_trx_commit=0 innodb_buffer_pool_size=2G wsrep_sst_method=rsync [embedded] [mariadb] [mariadb-10.1] 3. 192.168.1.53節點的/etc/my.cnf.d/server.cnf文件內容： [root@db3 yum.repos.d]# cat /etc/my.cnf.d/server.cnf | grep -v "#" | grep -v "^$" [server] [mysqld] server_id=152 datadir=/opt/galera user=mysql skip-external-locking skip-name-resolve character-set-server=utf8 [galera] wsrep_causal_reads=ON wsrep_provider_options="gcache.size=4G" wsrep_certify_nonPK=ON query_cache_size=0 wsrep_on=ON wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_name=MariaDB-Galera-Cluster wsrep_cluster_address="gcomm://192.168.1.51,192.168.1.52,192.168.1.53" wsrep_node_name=db3 wsrep_node_address=192.168.1.53 binlog_format=row default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 wsrep_slave_threads=8 innodb_flush_log_at_trx_commit=0 innodb_buffer_pool_size=2G wsrep_sst_method=rsync [embedded] [mariadb] [mariadb-10.1] MariaDB一個節點初始化安裝（僅192.168.1.51，其它節點不需要！）： #mysql_install_db --defaults-file=/etc/my.cnf.d/server.cnf --user=mysql 在192.168.1.51節點上通過bootstrap啟動（第一次啟動一定要使用--wsrep-new-cluster，再次啟動就不需要，其它節點也不需要！！） #mysqld_safe --defaults-file=/etc/my.cnf.d/server.cnf --user=mysql --wsrep-new-cluster & 在192.168.1.51節點上設置root密碼以及安全設置（其它節點不需要） #/usr/bin/mysql_secure_installation 或 #mysql_secure_installation 在配置過程中可以設置root的密碼，請牢記，以后要用到。 **在192.168.1.52,192.168.1.53節點啟動MariaDB，注意：如果前面配置文件中指定的/opt/galera目錄不存在的話，需要手工創建并指定權限和所有者:** #mkdir /opt/galera #chown mysql:root /opt/galera #chmod 700 /opt/galera 然后啟動數據庫。 #mysqld_safe --defaults-file=/etc/my.cnf.d/server.cnf --user=mysql & 驗證操作登錄三個節點查看 192.168.1.51節點： [root@db1 ~]# mysql -uroot –pxxxxxx MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 3 | +--------------------+-------+ 1 row in set (0.00 sec) MariaDB [(none)]> show global status like 'ws%'; +------------------------------+-------------------------------------------------------------+ | Variable_name | Value | +------------------------------+-------------------------------------------------------------+ | wsrep_cluster_conf_id | 3 | | wsrep_cluster_size | 3 | | wsrep_cluster_state_uuid |3108c722-ff29-11e6-a31f-bb500598d033 | | wsrep_cluster_status | Primary | | wsrep_flow_control_sent | 0 | | wsrep_gcomm_uuid |3107a278-ff29-11e6-96d3-374133af7e21 | | wsrep_incoming_addresses | 192.168.1.52:3306,192.168.1.53:3306,192.168.1.51:3306| | wsrep_provider_version | 25.3.19(r3667) | | wsrep_ready | ON | | wsrep_thread_count | 9 | +------------------------------+-------------------------------------------------------------+ 58 rows in set (0.00 sec) 注釋： wsrep_cluster_status為Primary，表示節點為主節點，正常讀寫。 wsrep_ready為ON，表示集群正常運行。 wsrep_cluster_size為3，表示集群有三個節點。創建數據庫測試 192.168.1.51節點： [root@db1 my.cnf.d]# mysql -uroot –pxxxxxx MariaDB [(none)]> create database test_db; Query OK, 1 row affected (0.01 sec) 192.168.1.52節點查看： [root@db2 my.cnf.d]# mysql -uroot -pxxxxxx MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test_db | +--------------------+ 192.168.1.53節點查看： [root@db3 my.cnf.d]# mysql -uroot -pxxxxxx MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test_db | +--------------------+ 4 rows in set (0.00 sec) 驗證InnoDB存儲的表 [root@db1 my.cnf.d]# mysql -uroot –pxxxxxx MariaDB [test_db]> create table stuinfo(id int,name text) ENGINE InnoDB; Query OK, 0 rows affected (0.04 sec) MariaDB [test_db]> insert into stuinfo values(1,'hive'); Query OK, 1 row affected (0.00 sec) MariaDB [test_db]> insert into stuinfo values(2,'hbase'); Query OK, 1 row affected (0.00 sec) MariaDB [test_db]> 其他節點查看： [root@db2 my.cnf.d]# mysql -uroot -pxxxxxx MariaDB [(none)]> use test_db; Reading table information for completion oftable and column names You can turn off this feature to get aquicker startup with -A Database changed MariaDB [test_db]> select * from stuinfo; +------+-------+ | id | name | +------+-------+ | 1 | hive | | 2 | hbase | +------+-------+ 2 rows in set (0.00 sec) [root@db3 my.cnf.d]# mysql -uroot –pxxxxxx MariaDB [(none)]> use test_db; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [test_db]> select * from stuinfo; +------+-------+ | id | name | +------+-------+ | 1 | hive | | 2 | hbase | +------+-------+ 2 rows in set (0.00 sec) 模擬節點故障將192.168.1.51數據庫停止掉： [root@db1 system]# mysqladmin -uroot -p "shutdown" 然后在其他節點192.168.1.52執行： MariaDB [test_db]> show global status like 'wsrep%'; +------------------------------+-----------------------------------------------+ | Variable_name | Value | +------------------------------+-----------------------------------------------+ | wsrep_flow_control_sent | 0 | | wsrep_gcomm_uuid | 0ce8537e-ff2a-11e6-b037-8a383b6a8db5 | | wsrep_incoming_addresses | 192.168.1.52:3306,192.168.1.53:3306 | | wsrep_last_committed | 10 | | wsrep_replicated_bytes | 0 | | wsrep_thread_count | 9 | +------------------------------+-----------------------------------------------+ 此時集群為自動將192.168.1.51故障節點剔除掉，并且正常提供服務。最后我們恢復失敗的節點： [root@db1 system]# mysqld_safe --defaults-file=/etc/my.cnf.d/server.cnf --user=mysql & 再次查看集群環境： MariaDB [test_db]> SHOW STATUS LIKE 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 3 | +--------------------+-------+ 1 row in set (0.00 sec 至此集群配置完成。 -----------------------------------------------------------------------------------------