mysql数据库版本
1.选择 GA版本，稳定5.5或5.6并且在6个月以上
2.前后几个月无大bug修复或无大量bug修复版本
创建用户名
# groupadd mysql
# useradd -r -g mysql mysql
# mv mysql-5.6.43-linux-glibc2.12-x86_64 /usr/local/mysql
数据库安装
# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/
拷贝文件
# cp -r /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
# cp -r /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld
# cp -r /usr/local/mysql/bin/mysql /usr/bin/
修改权限
# chown -R mysql.mysql /usr/local/mysql
# mkdir /var/lib/mysql
# chown -R mysql:mysql /var/lib/mysql
# chmod +x /etc/rc.d/init.d/mysqld
加入到系统服务
# chkconfig --add mysqld
# systemctl start mysqld
# systemctl enable mysqld
# /sbin/chkconfig mysqld on
在主库给从库授权
创建用户添加权限
# CREATE USER 'rep1'@'192.168.2.242' IDENTIFIED BY '123456';
# GRANT REPLICATION SLAVE ON *.* TO 'rep1'@'192.168.2.242';
# GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
刷新权限，立马生效
# FLUSH PRIVILEGES;
查看并记录 File和Position字段
# show master status;
从库
# CHANGE MASTER TO MASTER_HOST='192.168.2.243',MASTER_PORT=3306,MASTER_USER='rep1',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=199;
#启动从复制功能
# START SLAVE;
#停止从复制功能的命令
# STOP SLAVE;
#重置从复制功能的配置，会清除 master.info 和 relay-log.info 两个文件
# RESET SLAVE;
显示从库复制状态
# SHOW SLAVE STATUSG
数据库调优
1.删除测试库
# drop database test;
2.创建用户给予权限
# CREATE USER 'icomp'@'%' IDENTIFIED BY 'icomp';
# GRANT ALL ON *.* TO 'icomp'@'%';
3.修改用户密码
# update user set password=password('root') where user='root';
4.查看用户权限
# select user,host,password from mysql.user;
# show grants for 'rep1'@'192.168.2.208';
5.查看MySQL运行情况
# SHOW STATUS;
6.查看INNODB数据库引擎运行状态
# SHOW ENGINE INNODB STATUS;
7.查看当前正在进行的进程，对于有锁表等情况的排查很有用处
默认显示前100条
# SHOW PROCESSLIST;
显示所有
# SHOW FULL PROCESSLIST;
8.查看MySQL的配置参数
# SHOW VARIABLES;
9.查看当前已经被打开的表列表
# SHOW OPEN TABLES;
10.备份数据库
# mysqldump -uroot -proot --all-databases >./BackupName.sql
11.恢复数据库
# mysql -u root -proot < BackupName.sql
12.查看用户权限
# show grants for 'mengqi'@'%';
13.给予用户权限
# grant insert,update,delete,select,create, alter on *.* to mengqi@"%" identified by "123456";
# grant create routine on *.* to mengqi@"%" identified by "123456";
14.回收用户权限
#revoke all privileges on *.* from mengqi@"%";
配置文件my.cnf
[mysql]
no-auto-rehash
#default-character-set=utf8
socket=/var/lib/mysql/mysql.sock
[mysqld]
#innodb
user=mysql
port = 3306
#设置mysql的安装目录
basedir=/usr/local/mysql
socket=/var/lib/mysql/mysql.sock
#设置mysql数据库的数据的存放目录
datadir=/usr/local/mysql/data
innodb_buffer_pool_size=6G
innodb_log_file_size=2G
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=2
innodb_file_per_table=1
innodb_file_io_threads=4
innodb_flush_method=O_DIRECT
innodb_io_capacity=2000
innodb_io_capacity_max=6000
innodb_lru_scan_depth=2000
innodb_thread_concurrency = 0
innodb_additional_mem_pool_size=16M
innodb_autoinc_lock_mode = 2
# Binary log/replication
log-bin=mysql-bin
server-id=1
#不同步的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
#指定需要同步的数据库
binlog-do-db=test
sync_binlog=1
sync_relay_log=1
relay-log-info-repository=TABLE
master-info-repository=TABLE
expire_logs_days=7
binlog_format=ROW
transaction-isolation=READ-COMMITTED
#cache
tmp_table_size=512M
character-set-server=utf8
collation-server=utf8_general_ci
skip-external-locking
back_log=1024
key_buffer_size=1024M
thread_stack=256k
read_buffer_size=8M
thread_cache_size=64
query_cache_size=128M
max_heap_table_size=256M
query_cache_type=1
binlog_cache_size = 2M
table_open_cache=128
thread_cache=1024
thread_concurrency=8
wait_timeout=86400
interactive_timeout=7200
join_buffer_size = 1024M
sort_buffer_size = 8M
read_rnd_buffer_size = 8M
#connect
max-connect-errors=100000
max-connections=1000
##
explicit_defaults_for_timestamp=true
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABL