MySQL集群搭建

　　1，软件
　　MySQL版本：mysql 5.7.29
　　下载链接：https://downloads.mysql.com/archives/community/
　　注：建议使用迅雷下载，速度较快
　　2，系统挂盘#查看磁盘情况 lsblk #查看剩余情况 vgs  #2个步骤： #1、创建逻辑分区 lvcreate -L 140G -n lvmysql rootvg #2、格式化分区 mount /dev/rootvg/lvmysql /home/mysql
　　更多命令及自动挂盘请参考：https://blog.csdn.net/dongyuxu342719/article/details/82702357 3，调整磁盘IO调度规则# 查看： dmesg | grep scheduler # CentOS7 io调度规则默认为＂deadline＂,所以不需要修改4， 配置内核参数vim /etc/sysctl.conf  #配置交换区 vm.swappiness =1  #保存后使其生效 sysctl -p5，MySQL用户ulimit设置
　　操作系统对mysql用户注意以下参数的设置，该设置为必须的设置： vim /etc/security/limits.d/mysql.conf   mysql soft nofile 65535 mysql hard nofile 65535 mysql soft nproc 65535 mysql hard nproc 655356，防火墙及seLinux# 查看当前防火墙状态 firewall-cmd --state #关闭防火墙 systemctl stop firewalld.service systemctl disable firewalld.service  # 关闭selinux # 使用setenforce命令可以切换SELinux应用模式 # enforcing或1：强制模式 # permissive或0：允许模式  setenforce 0 vim /etc/selinux/config # 将 SELINUX=enforcing更改为 SELINUX=disabled  sed -i ＂s/SELINUX=enforcing/SELINUX=disabled/g＂ /etc/selinux/config cat /etc/selinux/config # 显示结果为  #SELINUX=disabled #SELINUXTYPE=targe7，关闭numa
　　关闭numa，解决mysql服务器swap严重以及内存泄漏的问题，需要注意的是Mysql 5.7.9引入了iinnodb_numa_interleave参数，MySQL自己解决了内存分类策略的问题前提是服务器支持numa，也就是说如果使用MySQL5.7.9之后的版本，可以不用关闭服务器的numa # 修改grab，conf # 1、 在/etc/default/grub 文件中修改 kernel行： vi /etc/default/grub GRUB_CMDLINE_LINUX 后添加 numa=off  # 2、重建配置文件 grub2-mkconfig -o /etc/grub2.cfg  # 3、重启操作系统 reboot8，卸载旧版本rpm包rpm -qa |grep mysql -i  rpm -e --nodeps msyql-xxx-5.1..xx86_64  # 把旧版本的mysql 配置文件移动 mv /etc/my.conf /etc/my.conf.`date+%Y%m%d`9，安装mysql#创建存放mysql的目录 mkdir /opt/mysql_base/  # 找到存放 mysql -tar包的目录 cd /home/mysqluser tar zxf mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz -C /opt/mysql_base/  mv /opt/mysql_base/mysql-5.7.29-linux-glibc2.12-x86_64 /opt/mysql_base/mysql-5.7.2910，创建MySQL运行用户
　　组名：mysql
　　组id：20000
　　用户名：mysql
　　用户id：20000
　　用户home目录：/home/mysql groupadd -g 20000 mysql  useradd -u 20000 -d /home/mysql -g mysql -m mysql  passwd mysql11， 创建相关文件系统mkdir /home/mysql/data cd /home/mysql/data mkdir mysql_tmp #存放临时数据 mkdir mysql_backup   #存放数据库备份 mkdir -p mysql_data/4306  #存放数据 mkdir -p mysql_log/mysql-bin    #存放binlog mkdir mysql_log/relay-bin      #relay log的存放位置 mkdir  /opt/mysql           #存放mysql介质  chown mysql.mysql -R  /home/mysql chown mysql.mysql -R  /opt/mysql_base chown mysql.mysql -R  /opt/mysql12，修改my.cnf
　　在 opt/mysql_base/mysql-5.7.29 下创建my.cnf su mysql  #切换用户 cd /opt/mysql_base/mysql-5.7.29 vi my.cnf  # 如何有上传的my.cnf，直接cp过来，命令是 cp /home/my.cnf /opt/mysql_base/mysql-5.7.29/[client] port = 4306 socket=/home/mysql/data/mysql_data/mysql.sock default-character-set = utf8mb4  [mysql]  default-character-set = utf8mb4   [mysqld] server-id=8008     ## 复制ID,主备机保持唯一 port = 3306 user=mysql basedir=/opt/mysql_base/mysql-5.7.29 datadir=/home/mysql/data/mysql_data/4306 tmpdir=/home/mysql/data/mysql_tmp socket=/home/mysql/data/mysql_data/mysql.sock pid-file=mysqld.pid default-storage-engine = INNODB lower_case_table_names = 1 transaction_isolation = READ-COMMITTED  character-set-client-handshake = FALSE  character-set-server = utf8mb4  collation-server = utf8mb4_unicode_ci  init_connect=＂SET NAMES utf8mb4＂  # connection back_log = 400 max_connections = 1000 interactive-timeout = 28800 wait_timeout = 29900 slave_net_timeout = 300 skip_name_resolve = on  # binlog log_bin=/home/mysql/data/mysql_log/mysql-bin/mysql-bin.index relay-log=/home/mysql/data/mysql_log/relay-bin/relay-bin.index expire_logs_days=7 binlog_cache_size = 8M binlog_format = ROW relay_log_purge = 1  log-bin-trust-function-creators = 1 binlog_group_commit_sync_no_delay_count = 20 binlog_group_commit_sync_delay = 10  #replica gtid_mode = on enforce_gtid_consistency = 1 log_slave_updates = 1 slave_parallel_type=LOGICAL_CLOCK slave_parallel_workers = 8 master_info_repository=TABLE relay_log_info_repository=TABLE slave_preserve_commit_order=on  ## Semi sync Config plugin-load = ＂validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so＂ rpl_semi_sync_master_enabled = 0 rpl_semi_sync_slave_enabled = 1 rpl_semi_sync_master_timeout = 10000 rpl_semi_sync_master_wait_for_slave_count = 1 rpl_semi_sync_master_wait_no_slave = 0  rpl_semi_sync_master_wait_point = AFTER_SYNC  ##默认就是AFTER_SYNC  #password plugin validate_password_policy = STRONG validate-password=FORCE_PLUS_PERMANENT  #caches & limits bulk_insert_buffer_size = 8M innodb_buffer_pool_size = 4G  #适当调整，可以选取物理内存的70% innodb_buffer_pool_instances = 8 innodb_log_buffer_size = 32M innodb_sort_buffer_size = 4M join_buffer_size = 4M sort_buffer_size = 4M key_buffer_size = 16M innodb_max_dirty_pages_pct = 50 max_allowed_packet = 128M max_heap_table_size = 16M read_buffer_size = 1M ##推荐 8-16G 配 1M read_rnd_buffer_size = 32M open-files-limit = 10240 table-definition_cache = 4096 query_cache_type = 0 table_open_cache = 4096 table_open_cache_instances = 16 innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:20G  # thread thread_cache_size = 256 innodb_thread_concurrency = 128 innodb_io_capacity = 5000 innodb_io_capacity_max = 10000 innodb_write_io_threads = 8 innodb_read_io_threads =8 ##推荐核数的两倍 innodb_purge_threads = 4 innodb_page_cleaners = 4  # innodb log innodb_flush_method = O_DIRECT innodb_autoextend_increment = 128 innodb_flush_log_at_trx_commit = 1 innodb_print_all_deadlocks sync_binlog = 1  # innodb  file innodb_data_file_path = ibdata:2G:autoextend innodb_file_per_table = 1 tmp_table_size = 16M innodb_log_file_size = 2048M innodb_undo_tablespaces = 3 innodb_undo_log_truncate = 1 innodb_online_alter_log_max_size = 1G  # logging slow_query_log = 1 log_timestamps = SYSTEM log-error=mysql-error.log slow_query_log_file=mysql_slow.log lc_messages_dir=/opt/mysql_base/mysql-5.7.29/share  # Recommended in standard MySQL setup sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES  # performance_schema settings performance-schema-instrument=＂memory/%=COUNTED＂ performance_schema-digests_size = 40000 performance_schema_max_table_handles = 40000 performance_schema_max_table_instances = 40000 performance_schema_max_sql_text_length = 4096 performance_schema_max_digest_length = 4096
　　其中主库、从库部分不同 # 主库 server-id=8008     ## 复制ID,主备机保持唯一  ## Semi sync Config rpl_semi_sync_master_enabled = 1 rpl_semi_sync_slave_enabled = 0  # 从库 server-id=8009     ## 复制ID,主备机保持唯一  ## Semi sync Config rpl_semi_sync_master_enabled = 0 rpl_semi_sync_slave_enabled = 1  # 从库 server-id=8010     ## 复制ID,主备机保持唯一  ## Semi sync Config rpl_semi_sync_master_enabled = 0 rpl_semi_sync_slave_enabled = 113， 初始化# 使用mysql用户 /opt/mysql_base/mysql-5.7.29/bin/mysqld --defaults-file=/opt/mysql_base/mysql-5.7.29/my.cnf  --initialize --user=mysql14，修改root密码
　　在 /home/mysqluser/data/mysql_data/4306/mysql-error.log 中查看初始化完成后生成的随机密码，记得保存下来，修改root密码时使用。 /opt/mysql_base/mysql-5.7.29/bin/mysqld_safe --defaults-file=/opt/mysql_base/mysql-5.7.29/my.cnf & # 建立软连接 ln -s /home/mysql/data/mysql_data/mysql.sock /tmp/mysql.sock # 配置一下mysql的环境变量 vi /etc/profile.d/mysql.sh # mysql.sh添加如下内容 MYSQL_HOME=/opt/mysql_base/mysql-5.7.29 PATH=$PATH:$MYSQL_HOME/bin export MYSQL_HOME PATH # 让配置文件生效 source /etc/profile  # 登录mysql mysql -uroot -p  # 修改密码 alter user ＂root＂@＂localhost＂ identified by ＂1qazZSE#39;;二，搭建复制1，主库建立复制用户
　　在主库上运行如下命令，新建用户 repl 并授权 create user ＂repl＂@＂182.%＂ identified by ＂1qazZSE#39;; grant replication slave on *.* to ＂repl＂@＂182.%＂;
　　从安全方面考虑，可以对ip进行范围限制，不适用% 2，主库操作# 使用场景 --第一次搭建主从数据库时，用于主库的初始化binglog操作； mysql> reset master; # 展示binlog 日志文件 mysql> show master statusG3，从库操作3.1, 在从库建立复制通道mysql> change master to master_host=＂192.168.222.224＂,         master_port=3306,         master_user=＂repl＂,         master_password=＂1qazZSE#39;,         master_auto_position=1;3.2，启动复制
　　在从库上启动复制并检查 slave状态 mysql> start slave;  mysql> show slave statusG; 查看Slave_IO_State、Slave_SQL_Running3.3， 查看主从库半复制同步开启状态
　　主库： mysql> show status like ＂rpl_semi_sync_master_status＂; +-----------------------------+-------+ | Variable_name               | Value | +-----------------------------+-------+ | Rpl_semi_sync_master_status | on   | +-----------------------------+-------+
　　从库： mysql> show global status like ＂rpl_semi_sync_slave_status＂; +-----------------------------+-------+ | Variable_name               | Value | +-----------------------------+-------+ | Rpl_semi_sync_master_status | on   | +-----------------------------+-------+3.4 注意问题
　　注：如果3.2 步骤出错，在重新操作3.1之前，需要使用以下命令重置： stop slave; reset slave;4，测试4.1, 主节点插入数据
　　主节点建立数据库，并插入一条 @数据： create database testdb; use testdb; create table test_tb(id int(3),name char(10)); insert into test_tb values(001,＂hi＂);4.2， 从节点查询数据show databases; use testdb; select * from test_tb;三，安装PerconaToolkit维护工具
　　当前选择的版本是2.2版本，版本号为 percona-toolkit-2.2.20-1,安装rpm包之前需要安装如下依赖包 cd /home/mysqluser/PerconaToolkit_depend # 批量安装 依赖 rpm -Uvh --force --nodeps  *rpm   cd /home/mysqluser/PerconaToolkit # 安装PerconaToolkit rpm -ivh percona-toolkit-2.2.20-1.noarch.rpm  # 验证是否安装成功 pt-query-digest --help四，安装 percona-xtrabackup
　　当前选择的版本是2.4.7版本，版本号为 percona-xtrabackup-2.4.7， tar zxvf percona-xtrabackup-2.4.7-Linux-x86_64.tar.gz -C /opt mv percona-xtrabackup-2.4.7-Linux-x86_64 percona-xtrabackup-2.4.7 ln -s /opt/percona-xtrabackup-2.4.7/bin/* /usr/bin/ xtrabackup --version # 验证版本