一.mysql-5.6安装:
1.安装mysql:
# yum -y install autoconf libaio libaio-devel
# groupadd mysql
# useradd -r -g mysql -s /sbin/nologin mysql
# wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz #下载编译完成的包,性能更可靠
# tar -zxvf mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz
# mv mysql-5.6.36-linux-glibc2.5-x86_64 /usr/local/mysql-5.6.36
# ln -s /usr/local/mysql-5.6.36 /usr/local/mysql
# chown -R mysql:mysql /usr/local/mysql-5.6.36/
2.创建配置文件:
vim /data/3306/my.cnf
[client]
port = 3306
socket = /data/3306/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3306/data
tmpdir = /tmp
open_files_limit = 65535
character-set-server = utf8mb4
back_log = 500
max_connections = 3000
max_connect_errors = 10000
#table_cache = 6144
max_allowed_packet =8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 64M
query_cache_type = 1
#default_table_type = InnoDB
#transaction_isolation = READ-COMMITTED
tmp_table_size = 512M
max_heap_table_size = 256M
table_open_cache = 512
log_error=/data/3306/mysql_3306.err
slow_query_log_file = /data/3306/mysql-slow.log
slow_query_log = 1
long_query_time =0.5
pid-file = /data/3306/mysql.pid
log-bin = /data/3306/mysql-bin
relay-log = /data/3306/relay-bin
relay-log-info-file = /data/3306/relay-log.info
binlog_cache_size = 2M
binlog_format = row
log-slave-updates
max_binlog_cache_size = 4M
max_binlog_size = 256M
expire_logs_days = 7
#myisam_sort_buffer_size = 1M
#myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size = 10G
#myisam_repair_threads = 1
#myisam_recover
skip-name-resolve
skip-host-cache
replicate-ignore-db = mysql
server-id = 71
innodb_additional_mem_pool_size = 8M
innodb_buffer_pool_size = 16G #设置成内存的60-70%最好
innodb_data_file_path = ibdata1:128M;ibdata2:128M:autoextend
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 4M
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_file_per_table = 1
[mysqldump]
quick
max_allowed_packet = 8M
3.初始化数据库:
# chown mysql.mysql -R /data/3306/
# cd /usr/local/mysql/scripts/
# ./mysql_install_db \
--defaults-file=/data/3306/my.cnf \
--basedir=/usr/local/mysql/ \
--datadir=/data/3306/data/ --user=mysql
#添加环境变量
# echo 'export PATH=/usr/local/mysql/bin/:$PATH' >> /etc/profile
# source /etc/profile
4.启动数据库:
# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf &
5.设置数据库秘密:
mysqladmin -uroot password Root123 -S /data/3306/mysql.sock
6.创建数据库启动脚本:
#!/bin/sh
#init
port=3306
mysql_user="root"
mysql_pwd="Root123"
cmdpath="/usr/local/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#startup function
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
echo "starting mysql…"
/bin/sh ${cmdpath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /devull &
else
echo "mysql is running…"
exit
fi
}
#stop function
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
echo "mysql is stopped…"
else
echo "stoping mysql…"
${cmdpath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
fi
}
#restart function
function_restart_mysql()
{
echo "restarting mysql…"
function_stop_mysql
sleep 2
function_start_mysql
}
case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
echo "usage: /data/${port}/mysql {start|stop|restart}"
esac
二.安装mysql监控:
1.安装Percona-zabbix监控:
参考:http://blog.csdn.net/mchdba/article/details/51447750
官方网站:https://www.percona.com/downloads/percona-monitoring-plugins/LATEST/
# yum install php-mysql php
# wget https://www.percona.com/downloads/percona-monitoring-plugins/percona-monitoring-plugins-1.1.7/binary/redhat/6/x86_64/percona-zabbix-templates-1.1.7-2.noarch.rpm
# rpm -ivh percona-zabbix-templates-1.1.6-1.noarch.rpm
后面看参考网站,这里不写了
2.玉兔mysql监控:
官方网站:http://www.lepus.cc/
特点:支持中文,支持mysql,redis等