centos源码搭建mysql

（5.6）

1、安装mysql所依赖环境

yum -y install gcc gcc-c++ autoconf libjpeg libjpeg-devel libpng libpng-devel freetype freetype-devel libxml2 libxml2-devel zlib zlib-devel glibc glibc-devel glib2 glib2-devel bzip2 bzip2-devel ncurses ncurses-devel curl curl-devel e2fsprogs e2fsprogs-devel krb5 krb5-devel libidn libidn-devel openssl openssl-devel openldap openldap-devel nss_ldap openldap-clients openldap-servers perl-CPAN man bc libaio
2、创建用户组，文件日志目录

groupadd mysql

useradd -g mysql mysql -s /sbin/nologin

mkdir -p /data/mysql/{data,binlog,relaylog}

mkdir -p /data/logs/mysql/

chown -R mysql.mysql /data/logs/mysql

chown -R mysql.mysql /data/mysql

3、下载mysql源码包（https://dev.mysql.com/downloads/mysql/）

wget https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.47-linux-glibc2.12-x86_64.tar.gz

tar -xf mysql-5.6.47-linux-glibc2.12-x86_64.tar.gz  -C /usr/local/

cd /usr/local

mv  mysql-5.6.47-linux-glibc2.12  mysql

chown -R mysql.mysql /usr/local/mysql

4、配置my.cnf文件

mv  /etc/my.cnf /etc/my.cnf_backup_`date +%F_%H`

cat >  /etc/my.cnf<<EOF

[client]
default-character-set=utf8
port = 3306
socket = /data/mysql/mysql.sock

[mysqld]
server-id=1
character_set_server=utf8
user = mysql
port = 3306
socket = /data/mysql/mysql.sock
datadir = /data/mysql/data/
pid-file = /data/mysql/mysql.pid
max_allowed_packet = 32M
innodb_file_per_table = 1
back_log = 300
max_connections = 1000
max_connect_errors = 7000
table_open_cache = 4096
open_files_limit = 10240
max_allowed_packet = 32M
wait_timeout = 86400
interactive_timeout=86400
connect_timeout=3600
sort_buffer_size = 16M
join_buffer_size = 16M
#query_cache_size = 128M
transaction_isolation = REPEATABLE-READ
thread_stack = 512K

innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:256M:autoextend 
innodb_flush_log_at_trx_commit = 2 
#innodb_file_io_threads = 4
innodb_thread_concurrency = 10
innodb_log_buffer_size = 16M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_lock_wait_timeout = 120
innodb_read_io_threads = 18
innodb_write_io_threads = 18

#binlog-ignore-db=information_schema,performance_schema
#replicate-ignore-db=information_schema,mysql,performance_schema
#binlog-ignore-db=mysql
#replicate-ignore-db=mysql
general_log = 0
log-bin=mysql-bin
binlog_cache_size = 7M
binlog_format=row
expire_logs_days=10
log-error = /data/logs/mysql/error.log
log-bin = /data/logs/mysql/binlog.log

#slow log
log_output = table,file
slow-query-log = 1
long_query_time = 2
slow-query-log-file = /data/logs/mysql/slowquery.log

sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[mysqldump]
quick
max_allowed_packet = 64M
[mysqld_safe]
pid-file=/data/mysql/mysqld.pid

EOF

(配置文件中innodb_buffer_pool_size这行需要按服务器配置改

Mem=`free -m | awk '/Mem:/{print $2}'`
y=0.5
unit="M"
middle=`expr $Mem*$y|bc`
result=`echo -n "$middle" |awk -F "." '{print $1}'`
echo $result

sed -i "s|innodb_buffer_pool_size = 1G|innodb_buffer_pool_size = ${result}M|g" /etc/my.cnf

)

5、编译安装

cd /usr/local/mysql

./scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql/data --explicit_defaults_for_timestamp --user=mysql

cp -f support-files/mysql.server /etc/init.d/mysqld

chmod 700 /etc/init.d/mysqld

chkconfig --add mysqld

chkconfig --level 345 mysqld on

echo 'PATH=$PATH':/usr/local/mysql/bin >> /etc/profile

/etc/init.d/mysqld start 

ln -s /data/mysql/mysql.sock  /tmp/mysql.sock

echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile

source /etc/profile

/usr/local/mysql/bin/mysqladmin -uroot password "123qqq...A"

ln -s /usr/local/mysql/lib/libmysqlclient.so.18 /usr/lib

cat > /tmp/mysql_sec_script<<EOF
        use mysql;
        delete from user where not (user='root') ;
        delete from user where user='root' and password=''; 
        drop database test;
        DROP USER ''@'%';
        grant all privileges on *.* to 'proxy'@'%' identified by '1234qwer';
        grant all privileges on *.* to backup@'localhost' identified by '1234qwer';
        flush privileges;
EOF

/usr/local/mysql/bin/mysql -u root -p"123qqq...A" -h localhost < /tmp/mysql_sec_script

rm -f /tmp/mysql_sec_script

（5.7）
yum clean all  &>/dev/null
yum repolist  &>/dev/null
yum -y install gcc gcc-c++ autoconf libjpeg libjpeg-devel libpng libpng-devel freetype freetype-devel libxml2 libxml2-devel zlib zlib-devel glibc glibc-devel glib2 glib2-devel bzip2 bzip2-devel ncurses ncurses-devel curl curl-devel e2fsprogs e2fsprogs-devel krb5 krb5-devel libidn libidn-devel openssl openssl-devel openldap openldap-devel nss_ldap openldap-clients openldap-servers
yum -y install perl-CPAN man bc perl-DBD-MySQL numactl.x86_64
yum -y install libaio

PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:~/bin
export PATH

Mem=`free -m | awk '/Mem:/{print $2}'`
y=0.65
unit="M"
middle=`expr $Mem*$y|bc`
result=`echo -n "$middle" |awk -F "." '{print $1}'`

vim /etc/my.cnf
[client]
port = 3306
socket = /data/mysql/mysql.sock

[mysql]
prompt = "\\r:\\m:\\s \\u@\\h:[\\d]> "
no-auto-rehash
default-character-set=utf8mb4

[mysqld]
server-id=1
federated
user = mysql
socket = /data/mysql/mysql.sock
default_authentication_plugin=mysql_native_password
#character-set-client-handshake = FALSE
#collation-server = utf8_general_ci
init_connect = 'SET NAMES utf8mb4'
character-set-server=utf8mb4
user = mysql
port = 3306
skip-name-resolve
skip-external-locking
secure_file_priv = '/tmp'
datadir = /data/mysql/data/
pid-file = /data/mysql/mysql.pid
max_allowed_packet = 512M

back_log = 500
max_connections = 3000
max_connect_errors = 5000
table_open_cache = 5120
open_files_limit = 10240
wait_timeout = 600
interactive_timeout = 600
connect_timeout = 60
lock_wait_timeout = 60 #等待获取资源等待的最长时间
lower_case_table_names=0

#thread buffer size
tmp_table_size = 32M
max_heap_table_size = 32M
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
key_buffer_size = 16M

transaction_isolation = READ-COMMITTED
log_bin_trust_function_creators=1  #slave 关闭
event_scheduler=1   #slave 关闭

innodb_file_per_table = 1
innodb_buffer_pool_size = 1G
innodb_data_file_path = ibdata1:512M:autoextend 
innodb_flush_log_at_trx_commit = 1 
innodb_thread_concurrency = 0
innodb_log_buffer_size = 4M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_lock_wait_timeout = 60  #事务等待获取资源等待的最长时间
innodb_read_io_threads = 32
innodb_write_io_threads = 32
innodb_flush_method=O_DIRECT
innodb_buffer_pool_instances = 4
innodb_lru_scan_depth = 2048
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_io_capacity = 10000  #ssd5000-20000
innodb_print_all_deadlocks = 1
innodb_flush_log_at_timeout = 1
innodb_sort_buffer_size=4M
innodb_default_row_format=dynamic
innodb_large_prefix = 1
innodb_purge_threads=6

#gtid_mode=on
#enforce_gtid_consistency=on
#slave_skip_errors=all
ft_min_word_len=1

#Multi-threaded Slave多线程并行复制配置依据last committed一致的组提交，并行relaylog执行
slave_parallel_workers=8
slave-parallel-type=LOGICAL_CLOCK
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

#binlog-ignore-db=information_schema,performance_schema
#replicate-ignore-db=information_schema,mysql,performance_schema
#binlog-ignore-db=mysql
#replicate-ignore-db=mysql
general_log = 0
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 4G  
max_binlog_size = 1G
log-bin=mysql-bin
binlog_format=row
expire_logs_days=15
log_slave_updates = 1
slave_max_allowed_packet = 1G
log-error = /data/logs/mysql/error.log
log-bin = /data/logs/mysql/binlog.log

#slow log
log_timestamps=system 
log_output = file,table
slow-query-log = 1
long_query_time = 2
#log_queries_not_using_indexes = 1 
#log_throttle_queries_not_using_indexes = 10 #设定每分钟记录到日志的未使用索引的语句数目，超过这个数目后只记录语句数量和花费的总时间
log_slow_admin_statements = 1 #记录执行缓慢的管理SQL
log_slow_slave_statements = 1
slow-query-log-file = /data/logs/mysql/slowquery.log

sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[mysqldump]
quick
max_allowed_packet = 512M
[mysqld_safe]
pid-file=/data/mysql/mysqld.pid
 

sed -i "s|innodb_buffer_pool_size = 1G|innodb_buffer_pool_size = ${result}M|g" /etc/my.cnf
    cd /usr/local
     wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz

    groupadd mysql
    useradd -g mysql mysql -s /sbin/nologin
    tar -zxvf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
    cd /usr/local
    mv mysql-5.7.23-linux-glibc2.12-x86_64 mysql
    mkdir -p /data/mysql/{data,binlog,relaylog}
    mkdir -p /data/logs/mysql/
    chown -R mysql.mysql /data/logs/mysql
    chown -R mysql.mysql /data/mysql
    

    cd /usr/local/mysql
    ./bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --explicit_defaults_for_timestamp --user=mysql 
    cp -f support-files/mysql.server /etc/init.d/mysqld 
    chmod 700 /etc/init.d/mysqld
    chkconfig --add mysqld
    /etc/init.d/mysqld start 
    ln -s /data/mysql/mysql.sock /tmp/mysql.sock
    ln -s /usr/local/mysql/bin/mysql* /usr/bin/
    echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
    source /etc/profile

cat > /tmp/mysql_sec_script<<EOF
        use mysql;
        delete from user where user != 'root';
        grant all on *.* to root@localhost identified by 'Live400.Com';
        GRANT  select,process,replication client ON *.* TO 'zabbix'@'127.0.0.1' IDENTIFIED BY 'P@ssw0rd';
        grant lock tables,reload,process,replication client,super,select,event,trigger,show view ON *.* TO 'backup'@localhost  identified by 'backup123456';
        flush privileges;
EOF
    /usr/local/mysql/bin/mysql -uroot  < /tmp/mysql_sec_script
    rm -f /tmp/mysql_sec_script