一、安装部署mysql
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= 3306socket= /data/3306/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user=mysql
port= 3306socket= /data/3306/mysql.sock
basedir= /usr/local/mysql
datadir= /data/3306/data
tmpdir= /tmp
open_files_limit= 65535character-set-server =utf8mb4
back_log= 500max_connections= 3000max_connect_errors= 10000#table_cache= 6144max_allowed_packet=8M
sort_buffer_size=1M
join_buffer_size=1M
thread_cache_size= 100thread_concurrency= 2query_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= 512log_error=/data/3306/mysql_3306.err
slow_query_log_file= /data/3306/mysql-slow.log
slow_query_log= 1long_query_time=0.5pid-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 = 71innodb_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= 2innodb_log_buffer_size=4M
innodb_log_file_size=2G
innodb_log_files_in_group= 3innodb_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/shport=3306mysql_user="root"mysql_pwd="Root123"cmdpath="/usr/local/mysql/bin"mysql_sock="/data/${port}/mysql.sock"#startfunctionfunction_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…"exitfi}
#stopfunctionfunction_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 shutdownfi}
#restartfunctionfunction_restart_mysql()
{echo "restarting mysql…"function_stop_mysqlsleep 2function_start_mysql
}case $1 instart)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;*)echo "usage: /data/${port}/mysql {start|stop|restart}"
esac
二、mysql的备份恢复
1、Xtrabackup介绍
MySQL冷备:mysqldump,热备:xtrabakackup有2个工具,分别是xtrabakup、innobakupe,支持MySQL、Percona server和MariaDB。
xtrabackup只能备份innoDB和xtraDB两种数据引擎的表,而不能备份MyISAM数据表。
2、Xtrabackup优点
1 备份速度快,物理备份可靠2 备份过程不会打断正在执行的事务(无需锁表)3 能够基于压缩等功能节约磁盘空间和流量4 自动备份校验5 还原速度快6 可以流传将备份传输到另外一台机器上7 在不增加服务器负载的情况备份数据
3、Xtrabackup备份原理
Xtrabackup备份流程图:
1 innobackupex启动后,会先fork一个进程,用于启动xtrabackup,然后等待xtrabackup备份ibd数据文件;2 xtrabackup在备份innoDB数据是,有2种线程:redo拷贝线程和ibd数据拷贝线程。xtrabackup进程开始执行后,会启动一个redo拷贝的线程,用于从最新的checkpoint点开始顺序拷贝redo.log;再启动ibd数据拷贝线程,进行拷贝ibd数据。这里是先启动redo拷贝线程的。在此阶段,innobackupex进行处于等待状态(等待文件被创建)3 xtrabackup拷贝完成ibd数据文件后,会通知innobackupex(通过创建文件),同时xtrabackup进入等待状态(redo线程依旧在拷贝redo.log)4 innobackupex收到xtrabackup通知后哦,执行FLUSH TABLES WITH READ LOCK(FTWRL),取得一致性位点,然后开始备份非InnoDB文件(如frm、MYD、MYI、CSV、opt、par等格式的文件),在拷贝非InnoDB文件的过程当中,数据库处于全局只读状态。5 当innobackup拷贝完所有的非InnoDB文件后