第一节mysql软件安装
1、检查系统中是否存在旧版本的数据库
rpm -qa |grep -i mysql
2、卸载旧版本数据库
rpm -e mysql-server-5.1.61-4.el6.x86_64 --nodeps
rpm -e rsyslog-mysql-5.8.10-2.el6.x86_64
rpm -e mysql-libs-5.1.61-4.el6.x86_64 --nodeps
rpm -e mysql-devel-5.1.61-4.el6.x86_64 --nodeps
rpm -e perl-DBD-MySQL-4.013-3.el6.x86_64 --nodeps
rpm -e mysql-5.1.61-4.el6.x86_64 --nodeps
rpm -e qt-mysql-4.6.2-24.el6.x86_64 --nodeps
rpm -e qt3-MySQL-3.3.8b-30.el6.x86_64 --nodeps
rm -rf /usr/lib64/mysql/
userdel mysql
groupdel mysql
2、操作系统优化
vim /etc/security/limits.d/90-nproc.conf
* soft nproc 65535
vim /etc/security/limits.conf
* soft core unlimited
* hard core unlimited
* soft nproc 65535
* hard nproc 65535
* soft nofile 65535
* hard nofile 65535
vim /etc/fstab
/dev/sdb1 /veris ext4 noatime,nodiratime,nobarrier 1 2
#文件系统调度策略使用deadline，如果是SSD或PCIe-SSD设备则使用noop，禁用numa或者使用numa的interleave all 模式numactl –interleave all 启动mysqld
vim /etc/grub.conf
module /vmlinuz-2.6.32-279.el6.x86_64 ro root=/dev/mapper/vg00-lv_root ****** elevator=deadline numa=off rhgb quiet
#快速替换
sed -i 's/rhgb quiet/elevator=deadline numa=off rhgb quiet/g' /etc/grub.conf
#查看
cat /sys/block/sdb/queue/scheduler
echo 2 > /sys/block/[device]/queue/rq_affinity (CentOS 6.4以上)
echo 0 > /sys/block/[device]/queue/add_random (关闭文件系统barrier)
RAID优化，使用红色选项：
-BBWC :Battery Backed Write Cache
-WT (Write through), WB (Write back): Selects write policy.
-NORA (No read ahead), RA (Read ahead), ADRA (Adaptive read ahead): Selects read policy.
-Cached, -Direct: Selects cache policy.
-RW, -RO, Blocked: Selects access policy.
-DisDskCache: Disables disk cache.
-64k :Strip Size
#减少预读:
echo 16 > /sys/block/sdb/queue/read_ahead_kb
#增大队列:
echo 512 > /sys/block/sdb/queue/nr_requests
#运行sync将dirty的内容写回硬盘
$sync
#释放操作系统的cache：
echo 3 > /proc/sys/vm/drop_caches
vim /etc/sysctl.conf
net.ipv4.tcp_rmem = 4096 16384 4194304
net.ipv4.tcp_wmem = 4096 16384 4194304
net.ipv4.tcp_sack = 0
net.ipv4.tcp_dsack = 0
net.ipv4.tcp_fack = 1
net.ipv4.ip_forward = 0
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 0
net.ipv4.tcp_max_tw_buckets = 12000
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_keepalive_time = 7200
net.ipv4.ip_local_port_range = 9000 65500
fs.file-max = 6815744
fs.aio-max-nr = 3145728
kernel.core_uses_pid = 1
kernel.core_pattern = /tmp/core.%h.%e.%p
kernel.shmmni = 4096
# 物理内存除以pagesize即4K(这里是128*1024*1024*1024除以4*1024)
kernel.shmall = 33554432
# 物理内存的一半 ，这里128G的一般64G（64*1024*1024*1024）
kernel.shmmax = 68719476736
kernel.sysrq = 0
kernel.sem = 250 32000 100 142
kernel.threads-max = 31863
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.core.somaxconn = 20480
net.core.netdev_max_backlog = 1000
vm.dirty_expire_centisecs = 1500
vm.dirty_writeback_centisecs = 200
vm.dirty_background_ratio = 10
vm.min_free_kbytes = 51200
vm.dirty_ratio = 30
vm.swappiness = 0
#确认启用CPU的低功耗(powersave)选项:
$ps ax|grep kondemand|wc -l
65
$cat /sys/devices/system/cpu/cpu0/cpufreq/scaling_governor
Ondemand
$cat /proc/cpuinfo|grep -Ei "model name|cpu MHz"
3、安装mysql软件到/veris/usr目录下
groupadd mysql
useradd -g mysql -m -s /bin/bash -c "MySQL Server" -d /veris/mysql mysql
rpm -ivh --relocate /usr=/veris/usr --badreloc --noscripts MySQL-shared-advanced-5.6.14-1.el6.x86_64.rpm
rpm -ivh --relocate /usr=/veris/usr --badreloc --noscripts MySQL-devel-advanced-5.6.14-1.el6.x86_64.rpm
rpm -ivh --relocate /usr=/veris/usr --badreloc --noscripts MySQL-server-advanced-5.6.14-1.el6.x86_64.rpm
rpm -ivh --relocate /usr=/veris/usr --badreloc --noscripts MySQL-client-advanced-5.6.14-1.el6.x86_64.rpm
4、加载mysql环境变量并添加到系统的/root/.bash_profile文件中
export PATH=/veris/usr/bin:/veris/usr/sbin:$PATH
export LD_LIBRARY_PATH=/veris/usr/lib64:$LD_LIBBRARY_PATH
5、创建billing数据库目录
mkdir -p /veris/billing/mysql/3307/data
mkdir -p /veris/billing/mysql/3307/etc
mkdir -p /veris/billing/mysql/3307/innodb
mkdir -p /veris/billing/mysql/3307/innodb/log
mkdir -p /veris/billing/mysql/3307/proc
mkdir -p /veris/billing/mysql/3307/log
mkdir -p /veris/billing/mysql/3307/log/audit
mkdir -p /veris/billing/mysql/3307/log/binlog
mkdir -p /veris/billing/mysql/3307/log/error
mkdir -p /veris/billing/mysql/3307/log/general
mkdir -p /veris/billing/mysql/3307/log/relay
mkdir -p /veris/billing/mysql/3307/log/slow
mkdir -p /veris/billing/mysql/3307/tmp
chmod -R 755 /veris/billing
chown -R mysql:mysql /veris/billing/mysql/3307/*
6、创建my_3307.conf参数文件
[mysqld]
user = mysql
port = 3307
basedir = /veris/usr
datadir = /veris/billing/mysql/3307/data
socket = /veris/billing/mysql/3307/proc/mysql_3307.sock
pid-file = /veris/billing/mysql/3307/proc/mysql_3307.pid
tmpdir = /veris/billing/mysql/3307/tmp
# Logging
log_bin = /veris/billing/mysql/3307/log/binlog/master_3307_bin
log-error = /veris/billing/mysql/3307/log/error/error_3307.log
slow_query_log_file = /veris/billing/mysql/3307/log/slow/slow_3307.log
general_log_file = /veris/billing/mysql/3307/log/general/general_3307.log
relay_log = /veris/billing/mysql/3307/log/relay/relay_3307.log
relay_log_info_file = /veris/billing/mysql/3307/log/relay/relay_log_3307.info
slow_query_log = ON
long_query_time = 1
#expire_logs_days = 15
explicit_defaults_for_timestamp = true
event_scheduler=1
#audit
plugin-dir = /veris/usr/lib64/mysql/plugin
plugin-load = audit_log.so
audit_log_file = /veris/billing/mysql/3307/log/audit/audit_3307.log
audit_log_rotate_on_size = 419430400
audit_log_flush = ON
audit_log_policy = ALL
character-set-server = utf8
init_connect = 'SET NAMES utf8'
open_files_limit = 65535
max_connections = 5000
max_user_connections = 5020
autocommit = 0
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
skip-name-resolve
max_allowed_packet = 64M
# InnoDB
innodb_buffer_pool_size = 6G
innodb_file_format = Barracuda
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 64
innodb_io_capacity = 200
innodb_io_capacity_max = 2000
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_change_buffering = inserts
innodb_log_buffer_size = 64M
innodb_log_file_size = 1G
innodb_log_group_home_dir = /veris/billing/mysql/3307/innodb/log
innodb_log_files_in_group = 3
innodb_data_file_path = ibdata1:1024M;ibdata2:1024M:autoextend
innodb_open_files = 65535
innodb_lock_wait_timeout = 100
innodb_flush_log_at_trx_commit = 2
innodb_additional_mem_pool_size=20M
# MyISAM
key_buffer_size = 16M
# Other
query_cache_size = 16M
tmp_table_size = 128M
max_heap_table_size = 64M
thread_cache_size = 64
bulk_insert_buffer_size = 8M
max_binlog_cache_size = 64M
max_binlog_size = 512M
log_bin_trust_function_creators = 1
transaction_isolation = read-committed
binlog_format = row
lower_case_table_names = 1
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
table_open_cache = 50000
table_definition_cache = 65535
connect_timeout = 28800
net_write_timeout = 300
net_read_timeout = 300
wait_timeout = 2592000
interactive_timeout = 2592000
join_buffer_size = 512K
sort_buffer_size = 512K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
# repl
server-id=3307
#gtid-mode=on
#enforce-gtid-consistency=true
#log-slave-updates
8、启动mysql实例
/veris/mysql/bin/mysqld_safe_ailk --defaults-file=/veris/billing/mysql/3307/etc/my_3307.cnf --ledir=/veris/usr/sbin &
mysqld_safe_ailk 这个文件是针对mysqld_safe文件的补充(修复了mysqld_safe的安全审计功能的一个BUG:原脚本在mysqld异常后重启时不能开启audit功能)
第二节 mysql用户创建和权限分配
1、创建用户，给予权限
mysql -uroot -h127.0.0.1 -P3307
use mysql;
UPDATE mysql.user SET password=PASSWORD('xxxxxx') WHERE User='root';
FLUSH PRIVILEGES;
delete from mysql.user where user='';
commit;
#启停数据库的：pprogmang;
grant process,super,shutdown,show databases on *.* to pprocmang@'%' identified by 'xxxxxx';
#只给业务开放三个用户：例如下面：
#做db变更的用户如：pcrm
grant ,select,insert,update,delete,create,drop,references,alter,index,create view,show view,alter routine,create routine,execute on push.* to pcrm@'%' identified by 'xxxxxx' with grant option;
grant create user on *.* to pcrm@'%';
#业务应用使用的用户权限：boss
grant select,insert,update,delete on ad.* to boss@'%' identified by 'xxxxxx';
#业务手工连接查询的用户：
grant process,select on *.* to pquery@'%' identified by 'xxxxxx';
第三节服务启停
1、启动服务
export PATH=/veris/usr/bin:/veris/usr/sbin:$PATH
export LD_LIBRARY_PATH=/veris/usr/lib64:$LD_LIBBRARY_PATH
/veris/mysql/bin/mysqld_safe_ailk --defaults-file=/veris/billing/mysql/3307/etc/my_3307.cnf --ledir=/veris/usr/sbin &
2、关闭服务
export PATH=/veris/usr/bin:/veris/usr/sbin:$PATH
export LD_LIBRARY_PATH=/veris/usr/lib64:$LD_LIBBRARY_PATH
mysqladmin -h 127.0.0.1 -P 3307 -uroot -pxxxxxx shutdown
第四节 备份与恢复
1、使用mysqlbackup工具备份（工具从oracle服务后台下载）
mysqlbackup --host=127.0.0.1 --user=root --password=xxxxx --port=3307 --compress-level=1 --with-timestamp --backup-dir=/verislog/mysqlbackup backup
2、使用mysqlbackup工具恢复
#创建数据目录
mkdir -p /veris/billing/mysql/5321/data
mkdir -p /veris/billing/mysql/5321/etc
mkdir -p /veris/billing/mysql/5321/innodb
mkdir -p /veris/billing/mysql/5321/innodb/log
mkdir -p /veris/billing/mysql/5321/proc
mkdir -p /veris/billing/mysql/5321/log
mkdir -p /veris/billing/mysql/5321/log/audit
mkdir -p /veris/billing/mysql/5321/log/binlog
mkdir -p /veris/billing/mysql/5321/log/error
mkdir -p /veris/billing/mysql/5321/log/general
mkdir -p /veris/billing/mysql/5321/log/relay
mkdir -p /veris/billing/mysql/5321/log/slow
mkdir -p /veris/billing/mysql/5321/tmp
chmod -R 755 /veris/billing
chown -R mysql:mysql /veris/billing/mysql/5321/*
#创建配置文件
vim /veris/billing/mysql/5321/etc/my_5321.cnf
chown -R mysql:mysql /veris/billing/mysql/5321/etc/my_5321.cnf
#一致性恢复
mysqlbackup --defaults-file=/veris/billing/mysql/5321/etc/my_5321.cnf --uncompress --backup-dir=/verislog/2015-01-05_02-01-37/ apply-log
#复制文件到指定的目录
mysqlbackup --defaults-file=/veris/billing/mysql/5321/etc/my_5321.cnf --backup-dir=/verislog/2015-01-05_02-01-37/ copy-back
#修改属性
chown -R mysql:mysql /veris/billing/mysql/5321/*
#启动5321数据库
/veris/mysql/bin/mysqld_safe_ailk --defaults-file=/veris/billing/mysql/5321/etc/my_5321.cnf --ledir=/veris/usr/sbin &
3、使用mysqldump工具备份
#备份全库
mysqldump -h127.0.0.1 -uroot -pxxxxxx -P5320 -R --triggers --default-character-set=utf8 --opt --max-allowed-packet=64M --net_buffer_length=163840 --single-transaction --flush-logs --all-databases > billing_db.sql
#备份指定的库
mysqldump -h127.0.0.1 -uroot -pxxxxxx -P5320 -B -R --triggers --default-character-set=utf8 --opt --max-allowed-packet=64M --net_buffer_length=163840 --single-transaction bd > bd.sql
3、使用mysqldump工具恢复
mysql> tee /tmp/recover_5320.log;
mysql> source billing_db.sql;
版权声明：QQ:597507041