MySQL安装
##上传MySQL安装包##
mkdir /home/oldboy/tools -p
cd /home/oldboy/tools/
###wget -q http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.34.tar.gz
####安装依赖
yum install ncurses-devel libaio-devel -y
###yum安装cmake
yum install cmake -y
###添加MySQL用户
useradd -s /sbin/nologin -M mysql
id mysql
####解压MySQL安装包
tar xf mysql-5.6.34.tar.gz
cd mysql-5.6.34
####编译
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.34 \
-DMYSQL_DATADIR=/application/mysql-5.6.34/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.6.34/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITH_ZLIB=bundled \
-DWITH_SSL=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLE_DOWNLOADS=1 \
-DWITH_DEBUG=0
echo $?
make && make install
echo $?
ln -s /application/mysql-5.6.34/ /application/mysql
####初始化数据库
cp support-files/my*.cnf /etc/my.cnf
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data --user=mysql cp support-files/mysql.server /etc/init.d/mysqld
chmod 700 /etc/init.d/mysqld
chkconfig mysqld on
chkconfig --list mysqld ##启动数据库
/etc/init.d/mysqld start
echo 'export PATH=/application/mysql/bin:$PATH' >>/etc/profile
tail -1 /etc/profile
source /etc/profile
echo $PATH
mysql ###出现问题查看日志
tail -100 /application/mysql/data/db01.err ###优化数据库
###设置密码
mysqladmin -uroot -p password oldboy123
###清理无用的库，无用的用户
show databases;
drop database test;
select user,host from mysql.user;
drop user 'root'@'::1';
select user,host from mysql.user;
drop user ''@'db01';
drop user 'root'@'db01';
drop user ''@'localhost';
select user,host from mysql.user;
#################以上单实例#####################
mysql的安装
第1章 MySQL多实例配置
1.1 什么是MySQL多实例？
简单地说，MySQL多实例就是在一台服务器上同时开启多个不同的服务端口（如：3306,3307），同时运行多个MySQL服务进程，这些服务进程通过不同的socket监听不同的服务端口来提供服务。
这些MySQL多实例共用一套MySQL安装程序，使用不同的my.cnf（也可以相同）配置文件、启动程序（也可以相同）和数据文件。在提供服务时，多实例MySQL在逻辑上看来是各自独立的，它们根据配置文件的对应设定值，获得服务器相应数量的硬件资源。
打个比方吧，MySQL多实例就相当于房子的多个卧室，每个实例可以看作一间卧室，整个服务器就是一套房子，服务器的硬件资源（cpu,men,disk）、软件资源（CentOS操作系统）可以看作房子的卫生间、厨房、客厅，是房子的共用资源。若你是北漂的小伙伴，与朋友一起租房子，相信更好理解，大家蜗居在一起，休息在自己的卧室，出来活动肯定是要共用上述公共资源。这样就可以很好的理解MySQL多实例了。
其实很多网络服务都是可以配置多实例的，例如nginx、Apache、haproxy、redis等都可以配置多实例。这在门户网站使用都很广泛。
1.2 MySQL多实例的作用于问题
- 有效利用服务器资源
当单个服务器资源有剩余时，可以充分利用剩余的资源提供更多的服务，且可以实现资源的逻辑隔离。
- 节约服务器资源
当公司资金紧张，但是数据库又需要各自尽量独立地提供服务，而且，需要主从复制等技术，多实例就再好不过了。
MySQL多实例有它的好处，但也有其弊端，比如，会存在资源互相抢占的问题。
当某个数据库实例并发很高或者有SQL慢查询时，整个实例会消耗大量的系统CPU、磁盘I/O等资源，导致服务器上的其他数据库实例提供服务的质量一起下降。这就相当于大家住在一个房子的不同卧室一样，早晨起来上班，都要刷牙、洗脸等，这样卫生间就会长期占有，其他人就要等待一样。不同实例获取的资源是相对独立的，无法像虚拟化一样完全隔离。
1.3 MySQL多实例的生产应用场景
1、资金紧张型公司的选择
若公司资金紧张，公司业务访问量又不是太大，但又希望不同业务的数据库服务各自尽量独立地提供服务而互相不受影响，同时，还需要主从复制等技术提供备份或读写分离服务，那么，多实例就再好不过了。比如：可以通过3台服务器部署9~15个实例，交叉做主从复制、数据备份及读写分离，这样就可以达到9~15台服务器每个只装一个数据库才有的效果。这里要强调的是，所谓的尽量独立是相对的。
2、并发访问不是特别大的业务
当公司业务访问量不太大的时候，服务器的资源基本都是浪费的，这时就很适合多实例的应用，如果对SQL语句的优化做得比较好，MySQL多实例会是一个很值得使用的技术，即使并发很大，合理分配好系统资源以及搭配好服务，也不会有太大问题。
3、门户网站应用MySQL多实例场景
门户网站通常都会使用多实例，因为配置硬件好的服务器，可节省IDC机柜空间，同时，跑多实例也会减少硬件资源跑不满的浪费。比如：百度公司的很多数据库都是多实例，不过，一般是从库多实例，例如某部门使用的IBM服务器为48核CPU，内存96GB，一台服务器跑3~4个实例；此外，sina网也是用的多实例，内存48GB左右。
说明：据调查，sina网的数据库单机1~4个数据库实例的居多，其中又数1~2个的最多，因为大业务占用的机器比较多。服务器是DELL R510的居多，CPU是E5210，48GB内存，磁盘12*300GSAS，做RAID10，此为门户网站的服务器配置参考。
另外，sina网站安装数据库时，一般采用编译安装的方式，并且会在进行优化之后做rpm包，以便统一使用。
1.4 配置MySQL多实例
上传文件地址：
链接：https://pan.baidu.com/s/14nfi-bHweexKO5dJAoZyMA 密码：nmko
##关闭单实例，跟多实例端口有冲突
/etc/init.d/mysqld stop
chkconfig mysqld off
chkconfig --list|grep mys
####创建目录##
mkdir -p /data/{3306,3307}/data
###上传配置文件到根下，并解压### chown -R mysql.mysql /data/
find /data -name mysql
find /data -name mysql|xargs chmod 700
find /data -name mysql|xargs ls -l
####初始化数据库
cd /application/mysql/scripts
./mysql_install_db --defaults-file=/data/3306/my.cnf --basedir=/application/mysql --datadir=/data/3306/data --user=mysql
./mysql_install_db --defaults-file=/data/3307/my.cnf --basedir=/application/mysql --datadir=/data/3307/data --user=mysql
####配置环境变量###
#echo 'export PATH=/application/mysql/bin:$PATH' >>/etc/profile
#source /etc/profile
####启动数据库###
/data/3306/mysql start
/data/3307/mysql start
netstat -lntup|grep 330
###登录数据库####
mysql -S /data/3306/mysql.sock
mysql -S /data/3307/mysql.sock
多实例
1.5 增加一个实例3308
####增加一个实例3308####
mkdir -p /data/3308/data cd /data/3308
vim my.cnf
[client]
port = 3308
socket = /data/3308/mysql.sock [mysql]
no-auto-rehash [mysqld]
user = mysql
port = 3308
socket = /data/3308/mysql.sock
basedir = /application/mysql
datadir = /data/3308/data
open_files_limit = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_open_cache = 614
external-locking = FALSE
max_allowed_packet =8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
#default_table_type = InnoDB
thread_stack = 192K
#transaction_isolation = READ-COMMITTED
tmp_table_size = 2M
max_heap_table_size = 2M
#long_query_time = 1
#log_long_format
#log-error = /data/3308/error.log
#log-slow-queries = /data/3308/slow.log
pid-file = /data/3308/mysql.pid
#log-bin = /data/3308/mysql-bin
relay-log = /data/3308/relay-bin
relay-log-info-file = /data/3308/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db=mysql
server-id = 8
innodb_additional_mem_pool_size = 4M
innodb_buffer_pool_size = 32M
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 4M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 2M [mysqld_safe]
log-error=/data/3308/oldboy_3308.err
pid-file=/data/3308/mysqld.pid
增加一个实例3308
vim mysql
#!/bin/sh
################################################
#this scripts is created by oldboy at 2007-06-09
#oldboy QQ:31333741
#site:http://www.etiantian.org
#blog:http://oldboy.blog.51cto.com
#oldboy trainning QQ group: 208160987 226199307 44246017
################################################
#init
port=3308
mysql_user="root"
mysql_pwd="oldboy123"
CmdPath="/application/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
start(){
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
else
printf "MySQL is running...\n"
exit 1
fi
}
stop(){
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit 1
else
printf "Stoping MySQL...\n"
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
fi
} restart(){
printf "Restarting MySQL...\n"
stop
sleep 2
start
}
对应脚本
1.6 快速增加一个实例3308
####快速增加实例####
mkdir -p /data/3308/data
\cp /data/3306/my.cnf /data/3308/
\cp /data/3306/mysql /data/3308/
sed -i 's/3306/3308/g' /data/3308/my.cnf
sed -i 's/server-id = 6/server-id = 8/g' /data/3308/my.cnf
sed -i 's/3306/3308/g' /data/3308/mysql
chown -R mysql:mysql /data/3308
chmod 700 /data/3308/mysql
cd /application/mysql/scripts
./mysql_install_db --defaults-file=/data/3308/my.cnf --datadir=/data/3308/data --basedir=/application/mysql --user=mysql
chown -R mysql:mysql /data/3308
egrep "server-id|log-bin" /data/3308/my.cnf
/data/3308/mysql start
sleep 5
netstat -lnt|grep 3308
1.7 为数据库设置密码
####
mysqladmin -uroot -p password oldboy123 -S /data/3308/mysql.sock
mysqladmin -uroot -p password oldboy123 -S /data/3307/mysql.sock
mysqladmin -uroot -p password oldboy123 -S /data/3306/mysql.sock
1.8 MySQL关闭优化
####优化关闭MySQL####
[root@db01 3306]# cat /data/3306/mysql_oldboy
#!/bin/sh
#init
port=3306
mysql_user="root"
mysql_pwd="oldboy123"
CmdPath="/application/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
mysqld_pid_file_path=/application/mysql/3306.pid
start(){
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf --pid-file=$mysqld_pid_file_path 2>&1 > /dev/null &
sleep 3
else
printf "MySQL is running...\n"
exit 1
fi
}
stop(){
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit 1
else
printf "Stoping MySQL...\n"
mysqld_pid=`cat "$mysqld_pid_file_path"`
if (kill -0 $mysqld_pid 2>/dev/null)
then
kill $mysqld_pid
sleep 2
fi
fi
} restart(){
printf "Restarting MySQL...\n"
stop
sleep 2
start
} case "$1" in
start)
start
;;
stop)
stop
;;
restart)
restart
;;
*)
printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac sh -x /data/3306/mysql_oldboy stop
优化
1.9 不用密码登录配置
###以前面配置的单实例MySQL为例
###不用密码登录
[root@db01 3306]# head /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[client]
user=root
password=oldboy123
[root@db01 3306]# chmod 700 /etc/my.cnf
/etc/my.cnf文件
1.10 数据库管理
#####数据库管理###
help contents; #查看帮助，help可以用?替代
|
Account Management |
账户管理 |
|
Administration |
管理 |
|
Compound Statements |
复合语句 |
|
Data Definition |
数据定义 |
|
Data Manipulation |
数据操作 |
|
Data Types |
数据类型 |
|
Functions |
函数 |
|
Functions and Modifiers for Use with GROUP BY |
与GROUP BY相关的函数和修饰符 |
|
Geographic Features |
地理特征 |
|
Help Metadata |
帮助元数据 |
|
Language Structure |
语言结构 |
|
Plugins |
插件 |
|
Procedures |
存储过程 |
|
Storage Engines |
存储引擎 |
|
Table Maintenance |
表维护 |
|
Transactions |
事务处理 |
|
User-Defined Functions |
用户自定义函数 |
|
Utility |
实用程序 |
1.11 MySQL密码忘记找回
###MySQL密码忘记####
mysqld_safe --skip-grant-tables --user=mysql &
#mysqld_safe --defaults-file=/data/3307/my.cnf --skip-grant-tables >/dev/null 2>&1 &
mysql
#用户@主机 唯一标识
mysql> update mysql.user set password=PASSWORD('old') where user='root' and host='localhost';
mysql> flush privileges;
/etc/init.d/mysqld stop
/etc/init.d/mysqld start
MySQL密码忘记