MySQL5.7.20单实例二进制包安装

女爷i 2024-04-20 08:27 2阅读 0赞

### 一、环境 ###

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
    fs.file-max=655360
    fs.aio-max-nr = 1048576
    kernel.sem = 5050 646400 5050 128
    kernel.shmmax = 137438953472
    kernel.shmall = 4294967296
    kernel.shmmni = 4096
    net.ipv4.ip_local_port_range = 9000 65500
    net.ipv4.tcp_mem = 94500000 915000000 927000000
    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 87380 16777216
    net.ipv4.tcp_wmem = 4096 65536 16777216
    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 mysql

#### 2.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/nologin

#### 2.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 <<EOF
    # my_3306.cnf
    [client]
    port            = 3306
    socket          = /gomeo2o/data/mysql/mysql_3306/tmp/mysql_3306.sock
    
    [mysql]
    #prompt="\u@\h:\p  \R:\m:\s [\d]>"
    #tee=/gomeo2o/data/mysql/mysql_3306/data/query.log
    #prompt="\u@\h:\p  \R:\m:\s [\d]>"
    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
    port = 3306
    socket = /gomeo2o/data/mysql/mysql_3306/tmp/mysql_3306.sock
    
    #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.%  #只复制哪个库的哪个表
    replicate-wild-ignore-table=information_schema.%   #忽略哪个库的哪个表
    
    
    #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
    
    explicit_defaults_for_timestamp=1
    #buffers & cache
    table_open_cache = 2048
    table_definition_cache = 2048
    table_open_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
    log_slave_updates = ON
    binlog_row_image=FULL
    
    # Two-Master configure
    #server-1 
    #auto-increment-offset = 1
    #auto-increment-increment = 2 
    
    #server-2                          
    #auto-increment-offset = 2
    #auto-increment-increment = 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_enabled = 1
    loose_rpl_semi_sync_slave_enabled = 1
    loose_rpl_semi_sync_master_timeout = 5000
    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 ##
    transaction_isolation = READ-COMMITTED
    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 replication
    ##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"  # 不能超过5位数字
    ##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.log

#### 2.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;

set password=password('123456');

set password for 'root'@'%' = password('123456');

update mysql.user set host = "%" where user='root';

grant all privileges on \*.\* to 'root'@'%' identified by 'root';

select host,user,authentication_string,plugin from mysql.user;
    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;
    mysqldump --master-data --single-transaction --default-character-set=utf8

$ mysqldump -h localhost -uroot -ppassword --master-data=2 --single-transaction --add-drop-table --create-options --quick \\

\--extended-insert --default-character-set=utf8 \\

\--databases discuz > backup-file.sql

######## 配置主从复制方法开始 ########
    #    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 status\G;
    ######## 配置主从复制方法结束 ########

#### 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 <<EOF
    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'
    EOF
    
    source  /root/.bash_profile
    
    cat  >>/etc/rc.local <<EOF
    #  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'
    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 status\G;
    ############################################# 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.sh

cat 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

[CentOS]: http://www.linuxidc.com/topicnews.aspx?tid=14