目标
- 理解线上部署考虑的因素
- 学会编译安装以及二进制安装mysql
- 学会升级mysql
- 学会多实例部署mysql数据库
- 学会合理部署mysql线上库
考虑因素：
- 版本选择，5.1,5.5还是5.6？
- 分支选择，官方社区版？percona server？mariadb?
- 推荐官方版，简单易上手
- 安装方式，包安装？二进制包安装？源码安装？
- 线上推荐二进制包安装
- 路径配置，参数配置（尽量模板化，标准化）
- 一个实例多个库，or多个实例单个库？
二进制安装mysql
- 下载软件安装包
- 解压放到指定目录（比如/usr/local）并将mysql目录放到PATH中
- 初始化实例，编辑配置文件 并启动
- 账号安全设置
1)下载
2)解压到指定目录，并将mysql目录放到PATH中
#tar -zxf mysql-5.5.-linux2.-i686.tar.gz
#mv mysql-5.5.-linux2.-i686 /usr/local/
#cd /usr/local
#mv mysql-5.5.-linux2.-i686 mysql55
#cd mysql55/bin
#export PATH=/usr/local/mysql55/bin:$PATH
3)初始化实例 使用脚本mysql_install_db
#mkdir -p /tmp/mysqldata/node1
#/usr/local/mysql55/script/mysql_install_db --user=mysql --basedir=/usr/local/mysql55 --datadir=/tmp/mysqldata/node1
#cd /tmp/mysqldata/node1
出现问题1 没有libaio.so.1库
/tmp/mysql55/bin/mysqld: error while loading shared libraries: libaio.so.: cannot open shared object file: No such file or directory
解决：查询libaio库并按照libaio.so.1
查询
aiapple@aiapple:/tmp$ sudo apt-cache search libaio
libaio-dev - Linux kernel AIO access library - development files
libaio1 - Linux kernel AIO access library - shared library
libaio1-dbg - Linux kernel AIO access library - debugging symbols
安装
aiapple@aiapple:/tmp$ sudo apt-get install libaio1
初始化成功：
aiapple@aiapple:/usr/local/mysql/data$ ls -l /tmp/mysqldata/node1/
total
drwx------ aiapple aiapple 7月 : mysql
drwx------ aiapple aiapple 7月 : performance_schema
drwx------ aiapple aiapple 7月 : test
拷贝配置文件：support-files中拷贝my-medium.cnf
cp /usr/local/mysql56/support-files/my-medium.cnf /tmp/mysqldata
3)使用mysqld_safe脚本，启动
/usr/local/mysql55/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf &
问题2：没有/usr/local/mysql/data 目录
aiapple@aiapple:/usr/local/mysql55/support-files$ /usr/local/mysql55/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf &
[]
aiapple@aiapple:/usr/local/mysql55/support-files$ :: mysqld_safe Logging to '/usr/local/mysql/data/aiapple.err'.
touch: cannot touch ‘/usr/local/mysql/data/aiapple.err’: No such file or directory
chmod: cannot access ‘/usr/local/mysql/data/aiapple.err’: No such file or directory
:: mysqld_safe The file /usr/local/mysql/bin/mysqld
does not exist or is not executable. Please cd to the mysql installation
directory and restart this script from there as follows:
./bin/mysqld_safe&
See http://dev.mysql.com/doc/mysql/en/mysqld-safe.html for more information
/usr/local/mysql55/bin/mysqld_safe: : /usr/local/mysql55/bin/mysqld_safe: cannot create /usr/local/mysql/data/aiapple.err: Directory nonexistent
^C
[]+ Exit /usr/local/mysql55/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf
解决：创建目录并赋权
aiapple@aiapple:/usr/local$ sudo mkdir -p mysql/data
aiapple@aiapple:/usr/local/mysql$ sudo chown -R aiapple .
aiapple@aiapple:/usr/local/mysql$ sudo chgrp -R aiapple .
问题3：不存/usr/local/mysql/bin/mysqld 文件
aiapple@aiapple:/usr/local/mysql$ /usr/local/mysql55/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf &
[]
aiapple@aiapple:/usr/local/mysql$ :: mysqld_safe Logging to '/usr/local/mysql/data/aiapple.err'.
:: mysqld_safe The file /usr/local/mysql/bin/mysqld
does not exist or is not executable. Please cd to the mysql installation
directory and restart this script from there as follows:
./bin/mysqld_safe&
See http://dev.mysql.com/doc/mysql/en/mysqld-safe.html for more information
^C
[]+ Exit /usr/local/mysql55/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf
怀疑 Ubuntu定死了basedir 在/usr/local/mysql 下
解决：拷贝mysql55中内容到该目录中
aiapple@aiapple:/usr/local/mysql$ cp -ar ../mysql55/* .
问题4：mysqld从 /usr/local/mysql/data中启动库文件；
aiapple@aiapple:/usr/local/mysql$ /usr/local/mysql55/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf &
[]
aiapple@aiapple:/usr/local/mysql$ :: mysqld_safe Logging to '/usr/local/mysql/data/aiapple.err'.
:: mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
:: mysqld_safe mysqld from pid file /usr/local/mysql/data/aiapple.pid ended []+ Done /usr/local/mysql55/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf
且mysqld没有启动
aiapple@aiapple:~$ ps -ef | grep mysqld
aiapple : pts/ :: grep --color=auto mysqld
查看aiapple.err:需要的一些表不在/usr/local/mysql/data
:: mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
:: [Note] /usr/local/mysql/bin/mysqld (mysqld 5.5.-log) starting as process ...
:: [Note] Plugin 'FEDERATED' is disabled.
/usr/local/mysql/bin/mysqld: Table 'mysql.plugin' doesn't exist
:: [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
:: InnoDB: The InnoDB memory heap is disabled
:: InnoDB: Mutexes and rw_locks use GCC atomic builtins
:: InnoDB: Compressed tables use zlib 1.2.
:: InnoDB: Using Linux native AIO
:: InnoDB: Initializing buffer pool, size = 128.0M
:: InnoDB: Completed initialization of buffer pool
:: InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn
:: InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number
:: InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents:
InnoDB: Apply batch completed
:: InnoDB: Waiting for the background threads to start
:: InnoDB: 5.5. started; log sequence number
:: [Note] Recovering after a crash using mysql-bin
:: [Note] Starting crash recovery...
:: [Note] Crash recovery finished.
:: [Note] Server hostname (bind-address): '0.0.0.0'; port:
:: [Note] - '0.0.0.0' resolves to '0.0.0.0';
:: [Note] Server socket created on IP: '0.0.0.0'.
:: [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
:: mysqld_safe mysqld from pid file /usr/local/mysql/data/aiapple.pid ended
解决：将原初始化的datadir目录中所有拷贝过去
aiapple@aiapple:/usr/local/mysql/data$ rm -rf test/
aiapple@aiapple:/usr/local/mysql/data$ cp -ar /tmp/mysqldata/node1/* .
成功安装：
aiapple@aiapple:/usr/local/mysql/data$ ps -ef | grep mysqld
aiapple : pts/ :: /bin/sh /usr/local/mysql55/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf
aiapple : pts/ :: /usr/local/mysql/bin/mysqld --defaults-file=/tmp/mysqldata/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/data/aiapple.err --pid-file=/usr/local/mysql/data/aiapple.pid --socket=/tmp/mysql.sock --port=
aiapple : pts/ :: grep --color=auto mysqld
疑问：
不知是Ubuntu指定了basedir=/usr/local/mysql,datadir=/usr/local/mysql/data;还是最开始的初始化没有管用，还是有什么配置没有写在配置文件中。
当然是没有写在配置文件中了，可以查看一下my-medium.cnf内容
[client]
#password = your_password
port =
socket = /tmp/mysql.sock # Here follows entries for some specific programs # The MySQL server
[mysqld]
port =
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache =
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
没有相关目录的配置；
在my.cnf 增加一下配置
my.cnf
port =
socket = /tmp/mysqldata/node1/mysql.sock [mysqld_safe]
user=aiapple # Here follows entries for some specific programs # The MySQL server
[mysqld]
port =
socket = /tmp/mysqldata/node1/mysql.sock
pid-file = /tmp/mysqldata/node1/mysqld.pid
basedir = /usr/local/mysql55
datadir = /tmp/mysqldata/node1
innodb_data_home_dir = /tmp/mysqldata/node1
innodb_log_group_home_dir =/tmp/mysqldata/node1
tmpdir = /tmp/mysqldata/node1
log-error = /tmp/mysqldata/node1/aiapple.log
log-bin = /tmp/mysqldata/node1/mysql-bin.log
重新启动
问题一：配置文件权限太大
aiapple@aiapple:/usr/local/mysql$ /usr/local/mysql55/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf &
Warning: World-writable config file '/tmp/mysqldata/my.cnf' is ignored
解决：降权
aiapple@aiapple:sudo chmod /tmp/mysqldata/my.cnf
问题二：bin目录未指定
aiapple@aiapple:/usr/local/mysql$ /usr/local/mysql55/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf &
:: mysqld_safe The file /usr/local/mysql/bin/mysqld does not exist or is not executable. Please cd to the mysql installation
directory and restart this script from there as follows: ./bin/mysqld_safe&
解决：在配置文件中通过ledir指定bin目录
#my.cnf增加
ledir = /usr/local/mysql55
问题三：aiapple.err报错，插件被禁止
:: [Note] Plugin 'FEDERATED' is disabled.
:: InnoDB: The InnoDB memory heap is disabled
:: InnoDB: Mutexes and rw_locks use GCC atomic builtins
:: InnoDB: Compressed tables use zlib 1.2.
:: InnoDB: Using Linux native AIO
:: InnoDB: Initializing buffer pool, size = 128.0M
解决：在配置文件中启用，nice=0
#my.cnf增加
nice =
4)账号安全设置
#删除空用户
mysql -uroot --socket=/tmp/mysql.sock
select user,host,password from mysql.user;
delete from mysql.user where user='';
delete from mysql.user where host<>'localhost'; mysql> select user,host,password from mysql.user;
+------+-----------+----------+
| user | host | password |
+------+-----------+----------+
| root | localhost | |
+------+-----------+----------+
row in set (0.00 sec)
#设置root密码
mysql> set password for root@'localhost'=password('');
Query OK, rows affected (0.00 sec)
#使设置生效
flush privileges;
#删除test库
看看只读权限的test用户能用test库做什么
创建test用户
#mysql -uroot -p123456--socket=/tmp/mysql.sock
grant select on *.* to test@'localhost' identified by ''; 使用test用户，在test库中建表
#mysql -utest -p123 --socket=/tmp/mysql.sock
use test
create table t1(id int);
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
row in set (0.01 sec)
删除test库
mysql> drop database test;
Query OK, row affected (0.01 sec)
mysql升级
从5.5升级到5.6
- 下载mysql5.6安装包并配置mysql5.6安装包安装路径
- 关闭mysql5.5的实例，修改部分参数，使用mysql5.6软件启动
- 执行mysql5.6路径下mysql_upgrade脚本
- 验证是否成功升级
MySQL升级
1)关闭mysql
查看mysql是否启动
aiapple@aiapple:/tmp/mysqldata$ ps -ef | grep mysqld
aiapple : pts/ :: /bin/sh /usr/local/mysql55/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf
aiapple : pts/ :: /usr/local/mysql/bin/mysqld --defaults-file=/tmp/mysqldata/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/data/aiapple.err --pid-file=/usr/local/mysql/data/aiapple.pid --socket=/tmp/mysql.sock --port=
aiapple : pts/ :: grep --color=auto mysqld
使用mysqladmin工具关闭mysql服务
aiapple@aiapple:/usr/local/mysql$ mysqladmin -uroot --socket=/tmp/mysql.sock shutdown -p
Enter password:
:: mysqld_safe mysqld from pid file /usr/local/mysql/data/aiapple.pid ended
[]+ Done /usr/local/mysql55/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf (wd: /usr/local/mysql/data)
(wd now: /usr/local/mysql)
检查是否关闭
aiapple@aiapple:/usr/local/mysql$ ps -ef | grep mysqld
aiapple : pts/ :: grep --color=auto mysqld
2)更改my.cnf
#vim my.cnf(把55目录都改成56的)
3）使用mysql5.6启动软件；
#使用5.6拉起实例
aiapple@aiapple:/tmp/mysqldata$ /usr/local/mysql56/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf &
[]
aiapple@aiapple:/tmp/mysqldata$ :: mysqld_safe Logging to '/tmp/mysqldata/node1/aiapple.log'.
:: mysqld_safe Starting mysqld daemon with databases from /tmp/mysqldata/node1
#查看mysqld进程有没有起来
aiapple@aiapple:/tmp/mysqldata$ ps -ef | grep mysql
aiapple : pts/ :: /bin/sh /usr/local/mysql56/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf
aiapple : pts/ :: /usr/local/mysql56/bin/mysqld --defaults-file=/tmp/mysqldata/my.cnf --basedir=/usr/local/mysql56 --datadir=/tmp/mysqldata/node1 --plugin-dir=/usr/local/mysql56/lib/plugin --log-error=/tmp/mysqldata/node1/aiapple.log --pid-file=/tmp/mysqldata/node1/mysqld.pid --socket=/tmp/mysqldata/node1/mysql.sock --port=
aiapple : pts/ :: grep --color=auto mysql
#登陆后查看mysql状态
mysql> status
--------------
mysql Ver 14.14 Distrib 5.6., for linux-glibc2. (x86_64) using EditLine wrapper Connection id:
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.-log MySQL Community Server (GPL)
Protocol version:
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysqldata/node1/mysql.sock
Uptime: min sec Threads: Questions: Slow queries: Opens: Flush tables: Open tables: Queries per second avg: 0.011
--------------
4）升级库mysql_upgrade
aiapple@aiapple:/tmp/mysqldata$ /usr/local/mysql56/bin/mysql_upgrade -uroot --socket=/tmp/mysqldata/node1/mysql.sock -p
Enter password:
Looking for 'mysql' as: /usr/local/mysql56/bin/mysql
Looking for 'mysqlcheck' as: /usr/local/mysql56/bin/mysqlcheck
Running 'mysqlcheck' with connection arguments: '--socket=/tmp/mysqldata/node1/mysql.sock'
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck' with connection arguments: '--socket=/tmp/mysqldata/node1/mysql.sock'
Warning: Using a password on the command line interface can be insecure.
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Running 'mysql_fix_privilege_tables'...
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck' with connection arguments: '--socket=/tmp/mysqldata/node1/mysql.sock'
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck' with connection arguments: '--socket=/tmp/mysqldata/node1/mysql.sock'
Warning: Using a password on the command line interface can be insecure.
OK
#验证库有没有升级成功
aiapple@aiapple:/tmp/mysqldata$ /usr/local/mysql56/bin/mysql_upgrade -uroot --socket=/tmp/mysqldata/node1/mysql.sock -p
Enter password:
Looking for 'mysql' as: /usr/local/mysql56/bin/mysql
Looking for 'mysqlcheck' as: /usr/local/mysql56/bin/mysqlcheck
This installation of MySQL is already upgraded to 5.6., use --force if you still need to run mysql_upgrade
mysql多实例安装
- 部署好mysql软件
- 编辑多个配置文件，初始化多个实例
- 启动mysql实例
为啥多实例部署？
- 充分利用系统资源
- 资源隔离
- 业务，模块隔离
多实例安装
#mkdir -p /mysqldata/node3
a) 初始化实例 使用脚本mysql_install_db
aiapple@aiapple:/tmp/mysqldata/node1$ /usr/local/mysql56/scripts/mysql_install_db --user=aiapple --basedir=/usr/local/mysql56 --datadir=/tmp/mysqldata/node3
#查看
aiapple@aiapple:/tmp/mysqldata/node1$ ls ../node3
ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema test
b) 给node3配置文件
cp my2.cnf my3.cnf
vim my3.cnf(修改相关参数)
[client]
#password = your_password
port =
socket = /tmp/mysqldata/node3/mysql.sock [mysqld_safe]
user=aiapple
ledir = /usr/local/mysql56/bin
#ledir = /usr/local/mysql55/bin
nice =
# Here follows entries for some specific programs # The MySQL server
[mysqld]
port =
socket = /tmp/mysqldata/node3/mysql.sock
pid-file = /tmp/mysqldata/node3/mysqld.pid
basedir = /usr/local/mysql56
#basedir = /usr/local/mysql55
datadir = /tmp/mysqldata/node3
innodb_data_home_dir = /tmp/mysqldata/node3
innodb_log_group_home_dir = /tmp/mysqldata/node3
tmpdir = /tmp/mysqldata/node3
log-error = /tmp/mysqldata/node3/aiapple.log
log-bin = /tmp/mysqldata/node3/mysql-bin.log
c) 使用mysqld_safe,及my3.cnf拉起实例
aiapple@aiapple:/tmp/mysqldata/node3$ /usr/local/mysql56/bin/mysqld_safe --defaults-file=/tmp/mysqldata/node3/my3.cnf &
[]
aiapple@aiapple:/tmp/mysqldata/node3$ :: mysqld_safe Logging to '/tmp/mysqldata/node3/aiapple.log'.
:: mysqld_safe Starting mysqld daemon with databases from /tmp/mysqldata/node3
d）查看
aiapple@aiapple:/tmp/mysqldata/node3$ ps -ef | grep mysqld
aiapple : pts/ :: /bin/sh /usr/local/mysql56/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf
aiapple : pts/ :: /usr/local/mysql56/bin/mysqld --defaults-file=/tmp/mysqldata/my.cnf --basedir=/usr/local/mysql56 --datadir=/tmp/mysqldata/node1 --plugin-dir=/usr/local/mysql56/lib/plugin --log-error=/tmp/mysqldata/node1/aiapple.log --pid-file=/tmp/mysqldata/node1/mysqld.pid --socket=/tmp/mysqldata/node1/mysql.sock --port=
aiapple : pts/ :: /bin/sh /usr/local/mysql56/bin/mysqld_safe --defaults-file=/tmp/mysqldata/node3/my3.cnf
aiapple : pts/ :: /usr/local/mysql56/bin/mysqld --defaults-file=/tmp/mysqldata/node3/my3.cnf --basedir=/usr/local/mysql56 --datadir=/tmp/mysqldata/node3 --plugin-dir=/usr/local/mysql56/lib/plugin --log-error=/tmp/mysqldata/node3/aiapple.log --pid-file=/tmp/mysqldata/node3/mysqld.pid --socket=/tmp/mysqldata/node3/mysql.sock --port=
aiapple : pts/ :: grep --color=auto mysqld 可以看到有两个端口的mysqld，分别是3306,；
e) 登陆新实例，删除test库，空用户，设置root密码
aiapple@aiapple:/tmp/mysqldata/node3$ mysql -uroot --socket=/tmp/mysqldata/node3/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.6.-log MySQL Community Server (GPL) Copyright (c) , , Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. delete from msyql.user where user='';
delete from mysql.user where host<>'localhost';
set password for root@'localhost'=password('');
delete from mysql.db;
drop database test;
flush privileges;
mysql线上安装小结
- 根据需求选择合适的版本以及分支，建议使用或升级到较高版本5.5或5.6
- 如果需要定制mysql功能的话，可以考虑编译安装，否则的话建议使用二进制包安装，比较省事。
- 根据机器配置选择部署多个mysql实例还是单个实例，机器配置非常好的话，建议部署多实例