centos6 MariaDB_MySQL_多主架构 Galera Cluster

最新推荐文章于 2022-07-21 16:28:02 发布

置顶奔放丶

最新推荐文章于 2022-07-21 16:28:02 发布

阅读量166

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/weixin_42590736/article/details/105393561

版权

yum 安装

默认家目录为 /data/mysql。多主架构 Galera Cluster。

配置 yum 源vim /etc/yum.repos.d/mariadb.repo

[mariadb]
name=MariaDB
baseurl=http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

安装

yum install -y MariaDB-Galera-server.x86_64 MariaDB-client.x86_64 galera.x86_64

配置 /etc/my.cnf.d/server.cnf
替换 wsrep_cluster_address 中的 IP。

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]
character-set-server=utf8


#
# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.0.1,192.168.0.2,192.168.0.3,192.168.0.4"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name='mycluster'
wsrep_sst_method=rsync
wsrep_max_ws_rows=131072000000
wsrep_max_ws_size=1073741824
port=38383
log-bin-trust-function-creators=1

bulk_insert_buffer_size = 100M
innodb_buffer_pool_size = 22G
innodb_log_file_size = 512M 
max_connections = 1000
innodb_file_per_table = ON
innodb_flush_log_at_trx_commit = 0
thread_concurrency = 24
skip_name_resolve


innodb_rollback_on_timeout = on
thread_handling=pool-of-threads
thread_pool_oversubscribe=30
thread_pool_size=64
thread_pool_idle_timeout=7200
thread_pool_max_threads=2000
max_allowed_packet = 500M



innodb_flush_method = O_DIRECT
thread_cache = 16
innodb_autoextend_increment = 128
query_cache_type = 0
query_cache_size = 1024M
binlog_cache_size = 4M
key_buffer_size = 16M


slow_query_log = ON
long_query_time = 3

log-bin=/data/mysql/mysql-logs/mysql-bin
slow_query_log_file = /data/mysql/mydata/slow.log
datadir=/data/mysql/mariadb
wait_timeout=300

event_scheduler = 0
tmpdir=/data/mysql/tmpdir/tmpdir
slave_load_tmpdir=/data/mysql/tmpdir/slave_tmpdir
max_tmp_tables=128
tmp_table_size=33554432
max_heap_table_size=33554432
group_concat_max_len = 100000000


#skip-grant-tables

#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#bind-address=0.0.0.0
#
# Optional setting
wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.0 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.0]

创建 /etc/my.cnf.d/server.cnf 中涉及的目录

mkdir -p /data/mysql/mysql-logs/mysql-bin /data/mysql/{mydata,mariadb} /data/mysql/tmpdir/{tmpdir,slave_tmpdir}

授权

chown -R mysql:mysql /data/mysql && chown -R mysql:mysql /etc/my.cnf.d/server.cnf

对集群全部节点进行初始化

/usr/bin/mysql_install_db --user=mysql --datadir=/data/mysql/mariadb --defaults-file=/etc/my.cnf.d/server.cnf --force

$ /usr/bin/mysql_install_db --user=mysql --datadir=/data/mysql/mariadb --defaults-file=/etc/my.cnf.d/server.cnf --force
Installing MariaDB/MySQL system tables in ‘/data/mysql/mariadb’ ...
200218 12:05:07 [Warning] ‘THREAD_CONCURRENCY’ is deprecated and will be removed in a future release.
200218 12:05:07 [Note] /usr/sbin/mysqld (mysqld 10.0.38-MariaDB-wsrep) starting as process 17626 ...
/usr/sbin/mysqld: Query cache is disabled (resize or similar command in progress); repeat this command later
200218 12:05:07 [Note] WSREP: Read nil XID from storage engines, skipping position init
200218 12:05:07 [Note] WSREP: wsrep_load(): loading provider library ‘none’
200218 12:05:08 [Note] InnoDB: Using mutexes to ref count buffer pool pages
200218 12:05:08 [Note] InnoDB: The InnoDB memory heap is disabled
200218 12:05:08 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
200218 12:05:08 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
200218 12:05:08 [Note] InnoDB: Compressed tables use zlib 1.2.3
200218 12:05:08 [Note] InnoDB: Using Linux native AIO
200218 12:05:08 [Note] InnoDB: Using CPU crc32 instructions
200218 12:05:08 [Note] InnoDB: Initializing buffer pool, size = 22.0G
200218 12:05:09 [Note] InnoDB: Completed initialization of buffer pool
200218 12:05:09 [Note] InnoDB: Highest supported file format is Barracuda.
200218 12:05:09 [Warning] InnoDB: Resizing redo log from 2*3072 to 2*32768 pages, LSN=1616707
200218 12:05:09 [Warning] InnoDB: Starting to delete and rewrite log files.
200218 12:05:09 [Note] InnoDB: Setting log file ./ib_logfile101 size to 512 MB
200218 12:05:14 [Note] InnoDB: Setting log file ./ib_logfile1 size to 512 MB
200218 12:05:19 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
200218 12:05:19 [Warning] InnoDB: New log files created, LSN=1616908
200218 12:05:19 [Note] InnoDB: 128 rollback segment(s) are active.
200218 12:05:19 [Note] InnoDB: Waiting for purge to start
200218 12:05:19 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.42-84.2 started; log sequence number 1616707
200218 12:05:20 [Note] WSREP: Service disconnected.
200218 12:05:21 [Note] WSREP: Some threads may fail to exit.
200218 12:05:21 [Note] InnoDB: FTS optimize thread exiting.
200218 12:05:21 [Note] InnoDB: Starting shutdown...
200218 12:05:21 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool
200218 12:05:24 [Note] InnoDB: Shutdown completed; log sequence number 1616918
OK
Filling help tables...
200218 12:05:24 [Warning] ‘THREAD_CONCURRENCY’ is deprecated and will be removed in a future release.
200218 12:05:24 [Note] /usr/sbin/mysqld (mysqld 10.0.38-MariaDB-wsrep) starting as process 17658 ...
/usr/sbin/mysqld: Query cache is disabled (resize or similar command in progress); repeat this command later
200218 12:05:24 [Note] WSREP: Read nil XID from storage engines, skipping position init
200218 12:05:24 [Note] WSREP: wsrep_load(): loading provider library ‘none’
200218 12:05:24 [Note] InnoDB: Using mutexes to ref count buffer pool pages
200218 12:05:24 [Note] InnoDB: The InnoDB memory heap is disabled
200218 12:05:24 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
200218 12:05:24 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
200218 12:05:24 [Note] InnoDB: Compressed tables use zlib 1.2.3
200218 12:05:24 [Note] InnoDB: Using Linux native AIO
200218 12:05:24 [Note] InnoDB: Using CPU crc32 instructions
200218 12:05:24 [Note] InnoDB: Initializing buffer pool, size = 22.0G
200218 12:05:25 [Note] InnoDB: Completed initialization of buffer pool
200218 12:05:25 [Note] InnoDB: Highest supported file format is Barracuda.
200218 12:05:25 [Note] InnoDB: 128 rollback segment(s) are active.
200218 12:05:25 [Note] InnoDB: Waiting for purge to start
200218 12:05:25 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.42-84.2 started; log sequence number 1616918
200218 12:05:25 [Note] WSREP: Service disconnected.
200218 12:05:26 [Note] WSREP: Some threads may fail to exit.
200218 12:05:26 [Note] InnoDB: FTS optimize thread exiting.
200218 12:05:26 [Note] InnoDB: Starting shutdown...
200218 12:05:27 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool
200218 12:05:30 [Note] InnoDB: Shutdown completed; log sequence number 1616928
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:

‘/usr/sbin/mysqladmin’ -u root password ‘new-password’
‘/usr/sbin/mysqladmin’ -u root -h andtalkdb02 password ‘new-password’

Alternatively you can run:
‘/usr/sbin/mysql_secure_installation’

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd ‘/usr’ ; /usr/sbin/mysqld_safe --datadir=’/data/mysql/mariadb’

You can test the MariaDB daemon with mysql-test-run.pl
cd ‘/usr/mysql-test’ ; perl mysql-test-run.pl

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB’s strong and vibrant community:
https://mariadb.org/get-involved/

输出“OK”视为初始化成功。

启服务
在集群中的一个节点上执行 /etc/init.d/mysql start --wsrep-new-cluster，成功将输出 Success。

$ /etc/init.d/mysql start --wsrep-new-cluster
Starting MariaDB.200218 12:06:15 mysqld_safe Logging to ‘/data/mysql/ mariadb/ > andtalkdb01.err’.
200218 12:06:15 mysqld_safe Starting mysqld daemon with databases from / data/ > mysql/mariadb
..... SUCCESS!

待上步执行成功后，在集群其它节点上执行service mysql start，成功将输出 Success。

$ service mysql start
Starting MariaDB.200218 12:11:02 mysqld_safe Logging to ‘/data/mysql/ mariadb/ > andtalkdb02.err’.
200218 12:11:02 mysqld_safe Starting mysqld daemon with databases from / data/ > mysql/mariadb
.... SUCCESS!

验证

mysql -uroot -p

因 root 初始密码为空，当命令行提示“Enter password:”时按跳过即可。

SHOW STATUS LIKE 'wsrep_%';

如 wsrep_incoming_addresses 的值与 /etc/my.cnf.d/server.cnf 配置的 IP 和 Port 相符，则说明搭建成功。

root 改密&授权（集群中每台服务器都执行）

授权——允许 root 从本机登陆授权其他用户

GRANT ALL ON *.* TO 'root'@'localhost' with grant option;

改密替换 PASSWD。

use mysql
update user set Password=password('PASSWD') where user='root'; flush privileges

创建并授权应用用户（集群中每台服务器都执行）

应用用户供各服务与 MySQLDB 进行业务数据交互时使用。

替换 USER、PASSWD。

CREATE USER 'USER'@'192.168.0.%';
SET PASSWORD FOR 'USER'@'192.168.0.%'=PASSWORD('PASSWD');
GRANT ALL ON *.* TO 'USER'@'192.168.0.%';
flush privileges;

以上涉及的网段取决于要访问 MySQLDB 的服务器的 IP。

验证

select * from mysql.user where user='USER'\G;

MySQLDump

默认不带参数的导出，生成的文件中 SQL 语句的顺序是：创建数据库判断语句-删除表-创建表-锁表-禁用索引-插入数据-启用索引-解锁表

linux mysqldump

MySQL mysqldump数据导出详解

常用参数

选项	说明
-u	登入 MySQLDB 用户的名称。
-p	登入 MySQLDB 用户的密码。
–databases, -B	数据库名称，参数后所有内容均视为数据库名。可省略。
–single-transaction	仅适用于 InnoDB。该选项在导出数据前提交一个 BEGIN SQL 语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。与 --lock-tables 选项互斥，因为 LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用–quick 选项。
–all-databases, -A	指代所有数据库。
–ignore-table	不导出指定表。如需忽略多个表，要多次指定，每次一个表名。例：–ignore-table=database.table1 --ignore-table=database.table2
–all-tablespaces, -Y	导出全部表空间。
–no-tablespaces, -y	不导出表空间数据。
–skip-add-locks	生成的文件中，在 Insert 语句前后去掉锁表语句
–skip-comments	不输出注释信息。作用与 --comments 相反，–comments 默认启用。
–no-data, -d	只导出表结构。
–add-drop-database	在建库语句前添加清空数据库语句。
–skip-add-drop-table	不在建表语句前添加清空表的语句。作用与 --add-drop-table 相反，–add-drop-table 默认启用。
–complete-insert, -c	使用完整的 INSERT 语句（包含列名），有可能受到 max_allowed_packet 参数影响导致插入失败。
–no-create-db, -n	不输出 CREATE DATABASE 语句。
–no-create-info, -t	不输出 CREATE TABLE 语句。
–default-character-set	设置默认字符集，默认值为 UTF8。
–extended-insert, -e	使用具有多个 VALUES 列的 INSERT 语法，使导出文件更小，并加速导入时的速度。默认为打开状态，使用 --skip-extended-insert 取消选项。
–insert-ignore	使用 INSERT IGNORE 取代 INSERT INTO 语句。INSERT IGNORE 作用：如表中已存在相同的记录，则忽略当前要插入的记录。
–replace	使用 REPLACE INTO 取代 INSERT INTO 语句。REPLACE INTO 表示插入替换数据，需求表中有 PrimaryKey / unique 索引。目标数据如果已存在于数据库中，则用新数据替换，如果不存在效果则与 INSERT INTO 一样。REPLACE 语句会返回一个值来指示受影响的行的数目，该数是被删除和

被插入的行数的和。REPLACE 单行，该值为1，则一行被插入，同时没有行被删除；如果该值大于 1，则在新行被插入前，有一行或多行被删除；如果表包含多个唯一索引，且新行复制了在不同唯一索引中的不同旧行的值，则有可能是单行替换了多个旧行。
–opt | 等同于 --add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys，默认开启,可用 --skip-opt 禁用。
–routines, -R | 导出存储过程和自定义函数。
–net-buffer-length | 通信时缓存数据的大小（即每个 insert 语句的大小），配合 --extended-insert 使用（默认开启）。最小4k，最大16M，默认是 1046528（1M）。

库级命令示例

导出某库所有表结构及数据

替换 USER、PSWD、DATABASE、EXPORT_FILE_NAME
```
mysqldump -uUSER -pPSWD DATABASE --single-transaction > EXPORT_FILE_NAME
```
导出某库所有表结构

替换 USER、PSWD、DATABASE、EXPORT_FILE_NAME
```
mysqldump -uUSER -pPSWD DATABASE -d > EXPORT_FILE_NAME
```

导出某库所有数据

替换 USER、PSWD、DATABASE、EXPORT_FILE_NAME

mysqldump -uUSER -pPSWD DATABASE -n -t --skip-comments > EXPORT_FILE_NAME

表级命令示例

导出某表的表结构及数据

替换 USER、PSWD、DATABASE、TABLE、CONDITION、EXPORT_FILE_NAME

mysqldump -uUSER -pPSWD DATABASE TABLE --single-transaction --where "CONDITION"> EXPORT_FILE_NAME

导出某表的表结构

替换 USER、PSWD、DATABASE、TABLE、EXPORT_FILE_NAME
```
mysqldump -uUSER -pPSWD DATABASE TABLE -d > EXPORT_FILE_NAME
```

导出某表的数据

替换 USER、PSWD、DATABASE、TABLE、EXPORT_FILE_NAME

mysqldump -uUSER -pPSWD DATABASE TABLE -t --single-transaction --skip-add-locks > EXPORT_FILE_NAME

将某表指定列导出生成 INSERT 语句
1. create table 临时表名 as select 某表指定列 from 某表;
2. mysqldump -uUSER -pPSWD DATABASE 临时表名 -c > EXPORT_FILE_NAME