一、环境
OS: CentOS release 6.9 (Final)
MySQL: mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
说明：类似的操作系统也可以的。为了方便起见，涉及到的操作，均可以直接粘贴复制，比较直观快捷的实施，我没有将带有命令行的截图或者文字粘贴出来。
可能会与路径有关，本文档是基于实践而记录下的，可能与你的环境有所不同，请遇到问题，具体分析查找资料。
二、准备工作
2.1、修改主机名称
hostname myserver sed -i 's/shell/myserver/' /etc/sysconfig/network
2.2、关闭selinux
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config setenforce 0
2.3、修改系统内核参数
cat >> /etc/sysctl.conf <<eof 128="" 5050="" 65500="" 65536="" 87380="" 646400="" 16777216="" 915000000="" 927000000="" fs.file-max="655360" fs.aio-max-nr="1048576" kernel.sem="5050" kernel.shmmax="137438953472" kernel.shmall="4294967296" kernel.shmmni="4096" net.ipv4.ip_local_port_range="9000" net.ipv4.tcp_mem="94500000" net.core.wmem_default="8388608" net.core.rmem_default="8388608" net.core.rmem_max="16777216" net.core.wmem_max="16777216" net.ipv4.tcp_rmem="4096" net.ipv4.tcp_wmem="4096" net.core.netdev_max_backlog="32768" net.ipv4.tcp_tw_recycle="1" net.ipv4.tcp_tw_reuse="1" net.ipv4.tcp_fin_timeout="10" net.ipv4.tcp_keepalive_time="300" net.ipv4.tcp_max_syn_backlog="32768" net.ipv4.tcp_syncookies="1" net.ipv4.tcp_timestamps="0" net.ipv4.conf.default.accept_source_route="0" vm.swappiness="1" eof="" #="" 使其生效="" sysctl="" -p 2.4、安装依赖包# 需要配置yum yum clean all yum -y update yum -y install gd libxml2-devel libjpeg-devel libpng-devel net-snmp-devel wget telnet vim zip unzip yum -y install curl-devel libxslt-devel pcre-devel libjpeg libpng libcurl4-openssl-dev yum -y install libcurl-devel libcurl freetype-config freetype freetype-devel unixODBC libxslt yum -y install libtool openssl-devel yum -y install perl-devel perl-ExtUtils-Embed yum -y install ncurses-devel.x86_64 openldap-devel.x86_64 lrzsz openssh-clients gcc-g77 bison yum -y install libmcrypt libmcrypt-devel mhash mhash-devel bzip2 bzip2-devel yum -y install ntpdate rsync svn patch iptables iptables-services yum -y install libevent libevent-devel cyrus-sasl cyrus-sasl-devel yum -y install gd-devel libmemcached-devel memcached git libssl-devel libyaml-devel auto yum -y install gcc gcc-c++ make autoconf automake ncurses-devel ncurses cmake libaio libaio-devel boost yum -y install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl perl-devel yum -y groupinstall "Server Platform Development" "Development tools" yum -y groupinstall "Development tools"2.5、基本初始化
mkdir -p /opt/mysql cd /opt/mysql/ wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz tar -zxf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz cd /usr/local/ ln -s /opt/mysql/mysql-5.7.20-linux-glibc2.12-x86_64 mysql2.6、创建所需要的目录
mkdir -p /gomeo2o/data/mysql/mysql_3306/{data,logs,tmp} mkdir -p /data/backup/
# 更改权限
groupadd mysql
useradd -g mysql mysql -d /home/mysql -s /sbin/nologin2.7、创建配置文件
# 排除干扰因素 if [ -f /etc/my.cnf ]; then mv /etc/my.cnf /etc/my.cnf.bak fi cat >/gomeo2o/data/mysql/mysql_3306/my_3306.cnf <" #tee="/gomeo2o/data/mysql/mysql_3306/data/query.log" prompt="[u@h][d]>_" connect_timeout="5" no-auto-rehash="" [mysqld]="" #misc="" user="mysql" basedir="/usr/local/mysql" datadir="/gomeo2o/data/mysql/mysql_3306/data" #timeout="" interactive_timeout="300" wait_timeout="300" #character="" set="" character-set-server="utf8" open_files_limit="65535" max_connections="100" max_connect_errors="100000" skip-name-resolve="1" disable-partition-engine-check="1" #logs="" log-output="file" slow_query_log="1" slow_query_log_file="/gomeo2o/data/mysql/mysql_3306/logs/mysql_3306_slow.log" log-error="/gomeo2o/data/mysql/mysql_3306/logs/mysql_3306_error.log" log_error_verbosity="3" pid-file="mysql_3306.pid" long_query_time="1" #log-slow-admin-statements="1" #log-queries-not-using-indexes="1" log-slow-slave-statements="1" #tmp="" tmpdir="/gomeo2o/data/mysql/mysql_3306/tmp" event_scheduler="1" performance_schema="on" max_allowed_packet="32M" character_set_server="utf8mb4" #character_set_server="utf8" default-time-zone="system" default-storage-engine="InnoDB" #bind_address="172.16.151.248" explicit_defaults_for_timestamp="1" #binlog="" binlog_format="row" server-id="1813306" log-bin="/gomeo2o/data/mysql/mysql_3306/logs/mysql-bin" log-bin-index="/gomeo2o/data/mysql/mysql_3306/logs/mysql-bin.index" binlog_cache_size="4M" max_binlog_size="1G" max_binlog_cache_size="2G" sync_binlog="1" expire_logs_days="90" #replicate-wild-ignore-table="mysql.%" replicate-wild-ignore-table="test.%" #relay="" log="" skip_slave_start="1" max_relay_log_size="1G" relay_log_purge="1" relay_log_recovery="1" log_slave_updates="" #slave-skip-errors="1032,1053,1062" #buffers="" &="" cache="" table_open_cache="2048" table_definition_cache="2048" max_heap_table_size="96M" sort_buffer_size="2M" join_buffer_size="2M" thread_cache_size="256" query_cache_size="0" query_cache_type="0" query_cache_limit="256K" query_cache_min_res_unit="512" thread_stack="192K" tmp_table_size="96M" key_buffer_size="8M" read_buffer_size="2M" read_rnd_buffer_size="16M" bulk_insert_buffer_size="32M" #myisam="" myisam_sort_buffer_size="128M" myisam_max_sort_file_size="10G" myisam_repair_threads="1" #innodb="" innodb_buffer_pool_size="10G" innodb_buffer_pool_instances="1" innodb_data_file_path="ibdata1:1G:autoextend" innodb_flush_log_at_trx_commit="1" innodb_log_buffer_size="64M" innodb_log_file_size="500M" innodb_log_files_in_group="3" innodb_max_dirty_pages_pct="50" innodb_file_per_table="1" innodb_rollback_on_timeout="" innodb_status_file="1" innodb_io_capacity="2000" transaction_isolation="READ-COMMITTED" innodb_flush_method="O_DIRECT" gtid_mode="ON" enforce_gtid_consistency="ON" master_info_repository="TABLE" relay-log-info-repository="TABLE" binlog_checksum="NONE" binlog_row_image="FULL" two-master="" configure="" #server-1="" #auto-increment-offset="1" #auto-increment-increment="2" #server-2="" semi="" sync="" replication="" settings="" #plugin_dir="/usr/local/mysql/lib/mysql/plugin" #plugin_load="validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" plugin_dir="/usr/local/mysql/lib/plugin" #官方版本的路径="" plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" loose_rpl_semi_sync_master_enabled="on" loose_rpl_semi_sync_master_timeout="5000" loose_rpl_semi_sync_master_trace_level="32" loose_rpl_semi_sync_master_wait_no_slave="on" loose_rpl_semi_sync_slave_enabled="on" loose_rpl_semi_sync_slave_trace_level="32" loose_rpl_semi_sync_master_wait_for_slave_count="1" loose_rpl_semi_sync_master_wait_point="AFTER_SYNC" slave_preserve_commit_order="1" slave_transaction_retries="128" log_timestamps="system" show_compatibility_56="on" slave_parallel_workers="16" slave_parallel_type="LOGICAL_CLOCK" loose_innodb_numa_interleave="1" innodb_buffer_pool_dump_pct="40" innodb_page_cleaners="16" innodb_undo_log_truncate="1" innodb_max_undo_log_size="2G" innodb_purge_rseg_truncate_frequency="128" #transaction_write_set_extraction="MURMUR32" read-uncommitted,="" read-committed,="" repeatable-read,="" serializable="" ##="" sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,ONLY_FULL_GROUP_BY" group="" ##log-bin="mysql" ##server-id="613306" ##gtid_mode="ON" ##enforce_gtid_consistency="ON" ##master_info_repository="TABLE" ##relay-log-info-repository="TABLE" ##binlog_checksum="NONE" ##log_slave_updates="ON" ##binlog_format="row" ##transaction_write_set_extraction="XXHASH64" ##loose-group_replication_group_name="3db33b36-0e51-409f-a61d-c99756e90154" ##loose-group_replication_start_on_boot="off" ##loose-group_replication_local_address="10.125.141.62:23306" 不能超过5位数字="" ##loose-group_replication_group_seeds="10.125.141.62:23306,10.125.141.62:23307,10.125.141.62:23308" ##loose-group_replication_bootstrap_group="off" loose-group_replication_single_primary_mode="FALSE" ###本次搭建的是mutil_mode="" loose-group_replication_enforce_update_everywhere_checks="TRUE" [mysqld_safe]="" #malloc-lib="/usr/local/mysql/lib/jmalloc.so" nice="-19" open-files-limit="65535" eof 2.8、初始化数据库chown -R mysql.mysql /gomeo2o/data chown -R mysql.mysql /data/backup/ chmod -R 775 /data/backup/ /usr/local/mysql/bin/mysqld --defaults-file=/gomeo2o/data/mysql/mysql_3306/my_3306.cnf --initialize-insecure &
# 查看日志
# tail -f /gomeo2o/data/mysql/mysql_3306/logs/error.log2.9、启动数据库推荐做法
/usr/local/mysql/bin/mysqld_safe --defaults-file=/gomeo2o/data/mysql/mysql_3306/my_3306.cnf &2.10、进入mysql,修改账号
/usr/local/mysql/bin/mysql -uroot -p -P3306 -S /gomeo2o/data/mysql/mysql_3306/tmp/mysql_3306.sock set sql_log_bin = 0; create user 'rpl_user'@'%'; grant replication slave on *.* to 'rpl_user'@'10.%' identified by 'BR8KLwXCKHgN'; update mysql.user set authentication_string=password('rootpwd2017') where user='root'; flush privileges; set sql_log_bin = 1; reset master ; reset slave all; exit;
######## 配置主从复制方法开始 ######## # CHANGE MASTER TO MASTER_HOST='172.16.1.10',MASTER_USER='rpl_user', # MASTER_PASSWORD='BR8KLwXCKHgN',MASTER_PORT=3306, # MASTER_CONNECT_RETRY=10,MASTER_AUTO_POSITION =1; # # start slave; # show slave statusG; ######## 配置主从复制方法结束 ########2.12、设置便捷使用方式
/usr/local/mysql/bin/mysql_config_editor set --host=localhost --login-path=3306_localhost_login --user=root --port=3306 --password --socket=/gomeo2o/data/mysql/mysql_3306/tmp/mysql_3306.sock # 请输入root密码： rootpwd2017 alias mysql.3306.start='/usr/local/mysql/bin/mysqld_safe --defaults-file=/gomeo2o/data/mysql/mysql_3306/my_3306.cnf &' alias mysql.3306.stop='/usr/local/mysql/bin/mysqladmin --defaults-file=/gomeo2o/data/mysql/mysql_3306/my_3306.cnf --login-path=3306_localhost_login shutdown &' alias mysql.3306.login='/usr/local/mysql/bin/mysql --defaults-file=/gomeo2o/data/mysql/mysql_3306/my_3306.cnf --login-path=3306_localhost_login' # alias mysql.3306.all_dump='/usr/local/mysql/bin/mysqldump --defaults-file=/gomeo2o/data/mysql/mysql_3306/my_3306.cnf --login-path=3306_localhost_login -R -E --triggers -e --max_allowed_packet=16777216 --net_buffer_length=16384 --default-character-set=utf8mb4 --set-gtid-purged=OFF --master-data=2 --single-transaction --all-databases --quick | gzip >/data/backup/all_database_bak_`date +%Y-%m-%d_%H_%M_%S`.sql.gz' cat >>/root/.bashrc </data/backup/all_database_bak_`date +%Y-%m-%d_%H_%M_%S`.sql.gz" eof="" source="" root="" .bash_profile="" cat="">>/etc/rc.local </data/backup/all_database_bak_`date +%Y-%m-%d_%H_%M_%S`.sql.gz" eof 3、gtid故障处理一例############################################# gtid故障处理开始 ############################################# # 模拟在从库删除库，然后再主库删除该库，报如下错误 # Last_SQL_Error: Error 'Can't drop database 'db1'; database doesn't exist' on query. Default database: 'db1'. Query: 'drop database db1' # Replicate_Ignore_Server_Ids: # Master_Server_Id: 623306 # Master_UUID: 11526eb0-fcbc-11e6-af7d-005056b937e2 # Master_Info_File: mysql.slave_master_info # SQL_Delay: 0 # SQL_Remaining_Delay: NULL # Slave_SQL_Running_State: # Master_Retry_Count: 86400 # Master_Bind: # Last_IO_Error_Timestamp: # Last_SQL_Error_Timestamp: 170227 15:44:06 # Master_SSL_Crl: # Master_SSL_Crlpath: # Retrieved_Gtid_Set: 11526eb0-fcbc-11e6-af7d-005056b937e2:1-2 # Executed_Gtid_Set: 11526eb0-fcbc-11e6-af7d-005056b937e2:1, # 1760a7a5-fcbc-11e6-8f14-005056b90358:1 # Auto_Position: 1 # Replicate_Rewrite_DB: # Channel_Name: # Master_TLS_Version: # # 处理方法： # stop slave; # set gtid_next='11526eb0-fcbc-11e6-af7d-005056b937e2:2'; # begin; # commit; # set gtid_next='automatic'; # start slave; # show slave statusG; ############################################# gtid故障处理结束 #############################################四、配套卸载删除脚本
[root@myserver mysql]# sh Uninstall_mysql.sh 请注意,你正在关闭mysqld服务!!! [Y/N]: Y 你已经选择关闭mysqld服务操作,即将关闭!!! 1 2 3 4 5 6 7 8 9 10 mysql卸载完毕... [root@myserver mysql]# chmod +x Uninstall_mysql.shcat Uninstall_mysql.sh #!/bin/bash mysqld_count=`ps -ef|grep -v 'grep' | grep mysqld |wc -l` echo " 请注意,你正在关闭mysqld服务!!!" function shutdown_and_delete_files () { if [ "${mysqld_count}" -eq 2 ]; then /usr/local/mysql/bin/mysqladmin --defaults-file=/gomeo2o/data/mysql/mysql_3306/my_3306.cnf --login-path=3306_localhost_login shutdown & fi # 模拟打印进度条 for i in {1..10} do echo -n " $i" sleep 1 done # 删除链接文件 if [ -L /usr/local/mysql ]; then unlink /usr/local/mysql fi echo "" # 删除相应的文件 rm -rf /opt/mysql/mysql-5.7.20-linux-glibc2.12-x86_64 rm -rf /etc/my.cnf.bak rm -rf /data/backup/* cp /root/.bashrc /root/.bashrc.bak cp /etc/rc.local /etc/rc.local.bak sed -i '/mysql.3306/d' /root/.bashrc sed -i '/mysql.3306/d' /etc/rc.local rm -rf /gomeo2o/data/* echo "mysql卸载完毕..." echo "" } read -p "[Y/N]: " answer case "${answer}" in Y|y|YES|yes) echo "你已经选择关闭mysqld服务操作,即将关闭!!!" shutdown_and_delete_files ;; N|n|NO|no) echo "你已经取消关闭mysqld服务操作..." ;; *) #echo "Error choice" echo "Useage: [Y|YES|y|yes N|NO|n|no]" ;; esac：http://www.linuxidc.com/Linux/2017-12/149669.htm
