线上部署考虑因素：
版本选择，5.1、5.5还是5.6？
5.1官方已不再维护，不建议
5.5是现在线上使用最多的版本
5.6最新的稳定版，已发布3年多，现在使用的也很多分支选择,官方社区版?persona server ? Mariadb ?
官方版 推荐使用
persona server 管理性能上有改进，并且完全兼容mysql
Mariadb 是mysql原班人马开发的安装方式，包安装？二进制包安装源码安装？
线上推荐使用二进制包安装路径配置，参数配置（尽量模板化、标准化）
一个实例多个库或多个实例单个库？
如果只部署一个实例，因为mysql是单进程的，对多核的利用不充分，导致资源浪费
多实例的方式，是现在主流的线上部署方式检查系统是否含有mysql相关组件
# rpm -qa |grep mysql
mysql-libs-5.1.71-1.el6.x86_64移除相关组件
# yum remove mysql-libs*下载mysql二进制包
# wget http://120.52.72.24/cdn.mysql.com/c3pr90ntc0td/archives/mysql-5.6/mysql-5.6.24-linux-glibc2.5-x86_64.tar.gz安装依赖包
# yum install libaio创建mysql用户、组
# groupadd mysql
# useradd -g mysql mysql解压二进制包
# tar xzf mysql-5.6.24-linux-glibc2.5-x86_64.tar.gz
# pwd
/opt/mysql-5.6.24-linux-glibc2.5-x86_64创建软连接
# pwd
/usr/local
# ln -s /opt/mysql-5.6.24-linux-glibc2.5-x86_64/ mysql指定文件拥有者为mysql
# chown -R mysql:mysql /opt/mysql-5.6.24-linux-glibc2.5-x86_64
# chown -R mysql:mysql /usr/local/mysql创建相关目录
配置文件目录
# mkdir -p /etc/mysql
# chown -R mysql:mysql /etc/mysql
数据文件目录
# mkdir -p /data1/db3306
临时文件目录
# mkdir -p /data1/tmp
# chown -R mysql:mysql /data1
设置环境变量（追加）
# vi /etc/profile
...
export MYSQL_HOME=/usr/local/mysql
export PATH=$MYSQL_HOME/bin:$PATH
export C_INCLUDE_PATH=$MYSQL_HOME/include
export LIBDIR=/usr/local/lib
export LD_LIBRARY_PATH=/usr/local/lib:$MYSQL_HOME/lib
export LD_RUN_PATH=/usr/local/lib
# source /etc/profile
# mysql --version
mysql Ver 14.14 Distrib 5.6.24, for linux-glibc2.5 (x86_64) using EditLine wrapper编辑配置文件
# vi /etc/mysql/mysql3306.cnf
[mysqld]
# GENERAL #
user = mysql
port = 3306
socket = /data1/db3306/my3306.sock
pid_file = /data1/db3306/mysql.pid
datadir = /data1/db3306/
tmpdir = /data1/tmp
log_bin = /data1/db3306/3306-mysql-bin
relay-log = /data1/db3306/3306-relay-bin
log_error = /data1/db3306/error.log
slow_query_log_file = /data1/db3306/slow-queries.log
long_query_time=1
sync_binlog = 0
expire_logs_days = 7
back_log=1024
skip-name-resolve
skip-slave-start
skip-external-locking
skip-character-set-client-handshake
explicit_defaults_for_timestamp=true
default_storage_engine = InnoDB
bind-address=0.0.0.0
#lower_case_table_names = 0
myisam_recover = FORCE,BACKUP
transaction-isolation = READ-COMMITTED
table_definition_cache = 4096
table_open_cache = 4096
# connection #
max_connections = 1100
max_user_connections = 1000
max_connect_errors = 1000
# timeout #
wait_timeout = 100
interactive_timeout = 100
lock_wait_timeout = 3
connect_timeout = 20
slave-net-timeout = 30
# character #
character-set-server=utf8
init-connect='SET NAMES utf8'
# disabled query cache #
query_cache_type = 0
query_cache_size = 0
# replication #
server_id=71493306
gtid_mode=ON
enforce-gtid-consistency
log-slave-updates
binlog-format=row
slave-parallel-workers=6
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync_master_info = 10000
slave_sql_verify_checksum=1
skip-slave-start
# session #
key_buffer_size = 128M
tmp_table_size = 32M
max_heap_table_size = 32M
max_allowed_packet = 32M
bulk_insert_buffer_size = 32M
sort_buffer_size = 128K
read_buffer_size = 1M
read_rnd_buffer_size = 1M
join_buffer_size = 128K
myisam_sort_buffer_size = 32M
tmp_table_size = 32M
max_heap_table_size = 64M
thread_cache_size = 64
#thread_concurrency = 32
thread_stack = 192K
# INNODB #
innodb_flush_method = O_DIRECT
innodb_data_home_dir = /data1/db3306/
innodb_data_file_path = ibdata1:10M:autoextend
#redo log
innodb_log_group_home_dir=/data1/db3306/
innodb_log_files_in_group = 3
innodb_log_file_size = 1G
#innodb performance
innodb_flush_log_at_trx_commit = 0
innodb_file_per_table = 1
innodb_buffer_pool_instances = 8
innodb_io_capacity = 2000
innodb_lock_wait_timeout = 30
binlog_error_action = ABORT_SERVER
innodb_buffer_pool_size = 256M
innodb_max_dirty_pages_pct=90
innodb_file_format=Barracuda
innodb_support_xa = 0
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1初始化
# pwd
/usr/local/mysql/scripts
查看帮助（列出部分重要的参数）
# ./mysql_install_db -h
--basedir=path The path to the MySQL installation directory.
--datadir=path The path to the MySQL data directory.
If missing, the directory will be created, but its
parent directory must already exist and be writable.
--defaults-file=name Only read default options from the given file name.
--user=user_name The login username to use for running mysqld. Files
and directories created by mysqld will be owned by this
user. You must be root to use this option. By default
mysqld runs using your current login name and files and
directories that it creates will be owned by you.
看到两个ok
# ./mysql_install_db --defaults-file=/etc/mysql/mysql3306.cnf --basedir=/usr/local/mysql
Installing MySQL system tables...2016-07-19 07:36:49 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.24-log) starting as process 3499 ...
OK
Filling help tables...2016-07-19 07:37:09 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.24-log) starting as process 3521 ...
OK
...启动mysql
# mysqld_safe --defaults-file=/etc/mysql/mysql3306.cnf &
# ps -ef |grep mysql
root 3551 2818 0 07:39 pts/1 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/mysql/mysql3306.cnf
mysql 4604 3551 3 07:39 pts/1 00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/etc/mysql/mysql3306.cnf --basedir=/usr/local/mysql --datadir=/data1/db3306/ --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data1/db3306/error.log --pid-file=/data1/db3306/mysql.pid --socket=/data1/db3306/my3306.sock --port=3306账号安全设置
# mysql -uroot --socket=/data1/db3306/my3306.sock
mysql> select user,host,password from mysql.user;
+------+-----------+----------+
| user | host | password |
+------+-----------+----------+
| root | localhost | |
| root | miles | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | miles | |
+------+-----------+----------+
6 rows in set (0.01 sec)
mysql> delete from mysql.user where user='';
Query OK, 2 rows affected (0.01 sec)
mysql> delete from mysql.user where host <>'localhost';
Query OK, 3 rows affected (0.00 sec)
mysql> select user,host,password from mysql.user;
+------+-----------+----------+
| user | host | password |
+------+-----------+----------+
| root | localhost | |
+------+-----------+----------+
1 row in set (0.00 sec)
mysql> set password for root@'localhost' = password('XXXXXX');
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
可以看到test数据库存在安全隐患，任何用户都可以在test进行操作
mysql> select * from mysql.db \G
*************************** 1. row ***************************
Host: %
Db: test
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
*************************** 2. row ***************************
Host: %
Db: test\_%
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
2 rows in set (0.00 sec)
mysql> delete from mysql.db;
Query OK, 2 rows affected (0.00 sec)
mysql> drop database test;
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)时间： 2024-10-31 14:10:21