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 至此集群配置完成。 -----------------------------------------------------------------------------------------