Linux安装

下载地址：https://dev.mysql.com/downloads/mysql/

image.png

下载完成后，为安全性，校验安装包

md5sum mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz

//显示
60d18d1b324104c83da33dcd7a989816  mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz

对比官方的MD5值是否一致

安装步骤参考官方文档如下：

shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql
shell> cd /usr/local
shell> tar xvf /path/to/mysql-VERSION-OS.tar.xz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> mkdir mysql-files
shell> chown mysql:mysql mysql-files
shell> chmod 750 mysql-files
shell> bin/mysqld --initialize --user=mysql
shell> bin/mysql_ssl_rsa_setup
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server

创建mysql用户和mysql用户组

groupadd mysql

useradd -r -g mysql -s /bin/false mysql

将下载完成的安装包解压到/usr/local目录下

cd /usr/local/src

tar xvf mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz

mv mysql-8.0.16-linux-glibc2.12-x86_64 /usr/local

注：解压 .xz格式：tar xvf ... ; 解压 .gz格式：tar zxvf ...

创建mysql软连接

cd /usr/local

ln -s mysql-8.0.16-linux-glibc2.12-x86_64 mysql

删除链接，可使用unlink或rm

创建mysql-files文件，并给当前目录下所有文件分配mysql所属用户和所属组

cd /usr/local

mkdir mysql-files

chown -R root:mysql .

chown -R mysql:mysql mysql-files

chmod 750 mysql-files

MySQL配置读取规则：优先从右往左读取
/etc/my.cnf 《== /etc/mysql/my.cnf 《== /usr/local/mysql/etc/my.cnf 《== ~/.my.cnf
查看读取规则：mysqld --help -v | grep my.cnf

配置mysql

[mysqld]
########basic settings########
server-id = 11 
port = 3306
user = mysql
bind_address = 10.166.224.32
autocommit = 0
character_set_server=utf8mb4
skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
datadir = /data/mysql_data
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
join_buffer_size = 134217728
tmp_table_size = 67108864
tmpdir = /tmp
max_allowed_packet = 16777216
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16777216
read_rnd_buffer_size = 33554432
sort_buffer_size = 33554432
########log settings########
log_error = error.log
slow_query_log = 1
slow_query_log_file = slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
long_query_time = 2
min_examined_row_limit = 100
########replication settings########
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin = bin.log
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
binlog_format = row 
relay_log = relay.log
relay_log_recovery = 1
binlog_gtid_simple_recovery = 1
slave_skip_errors = ddl_exist_errors
########innodb settings########
innodb_page_size = 8192
innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_log_group_home_dir = /redolog/
innodb_undo_directory = /undolog/
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_flush_neighbors = 1
innodb_log_file_size = 4G
innodb_log_buffer_size = 16777216
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864 
########semi sync replication settings########
plugin_dir=/usr/local/mysql/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

[mysqld-5.7]
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
transaction_write_set_extraction=MURMUR32
show_compatibility_56=on

注：上面的配置是 5.6 或 5.7的线上最优配置，8.0的可根据error日志报错提示来修改

初始化mysql

cd /usr/local

bin/mysqld --initialize --user=mysql

注：因为上面配置中的log、innodb文件设置的值比较大，初始化时间有点久，如果出现内存不足，可相对应的调整设置值小点
查看磁盘io：io -xm 3

设置datadir所属用户和所属组为mysql

cd /data

chown -R mysql:mysql

启动mysql

bin/mysql_ssl_rsa_setup

bin/mysqld_safe --user=mysql &

查看mysqld启动状态

ps -ef | grep mysqld

设置mysql安全启动方式和开机自启动

cp support-files/mysql.server /etc/init.d/mysqld

chkconfig --add /etc/init.d/mysqld

将```mysql````配置到系统环境变量中

vim /etc/profile

//在最后一行加上
export PATH=/usr/local/mysql/bin:$PATH

使/etc/profile文件立即生效

source /et/profile

查看mysql版本

mysql -V

//显示
mysql  Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using  EditLine wrapper

MySQL从5.7开始，安装完成后会分配一个临时的初始化密码，用户一定要重置初始化root密码，初始化临时密码在log文件里可以查看，从5.7开始，设置的密码，password慢慢取消，使用md5()

初始化MySQL的root用户密码

mysql -u root -p
//输入密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '密码';

到此，MySQL安装结束。

MySQL登录

1、几种登录方式

方法一：该方法默认使用root用户, 可使用select user();查看当前用户

mysql -p

方法二：该方法适用于在安装MySQL主机上进行本地登录

mysql -S /tmp/mysql.sock -u root -p

方法三：使用'root'@'127.0.0.1'这个用户登录

mysql -h 127.0.0.1 -u root -p

方法四：该方式等价与【方式二】，且和【方式三】属于两个不同的“用户”

mysql -h localhost -u root -p

2、免密码登录

方法一：配置my.cnf

统一配置，增加【client】

[client]
user = 'root'
password = '密码'

单对定义不同的客户端，这个是给/usr/loca/mysql/bin/mysql 使用的

[mysql]
user = root
password = '密码'

这个是给/usr/local/mysql/bin/mysqladmin使用的

[mysqladmin]
user = root
password = '密码'

方法二：login-path该方式相对安全。如果server被黑了，该二进制文件还是会被破解

shell> mysql_config_editor set -G vm1 -S /tmp/mysql.sock -u root -p
Enter password [输入root的密码]

shell> mysql_config_editor print --all
[vm1]
user=root
password=*****
socket=/tmp/mysql.sock

#login
shell> mysql --login-path=vm1 # 这样登录就不需要密码，且文件二进制存储 ,位置是 ~/.mylogin.cnf

方法三：```~/.my.cnf````, 自己当前家目录

vim ~/.my.cnf

[client]
user = 'root'
password = '密码'

MySQL升级

安全关闭mysql

/etc/init.d/mysqld stop

将mysql软连接指向新版本的MySQL包

cd /usr/local

unlink mysql

ln -s 新的MySQL包 mysql

备份下data目录中的mysql，以备将来回退

cp -r /data/mysql_data/mysql 备份目录/mysql

安全启动mysql

/etc/init.d/mysqld start

mysql_upgrade -p -s

参数 -s 一定要加,表示只更新系统表，-s: upgrade-system-tables
如果不加-s,则会把所有库的表以new mysql的方式重建，线上千万别这样操作
因为数据库二进制文件是兼容的，无需升级

什么时候不需要-s ? 当一些老的版本的存储格式需要新的特性，来提升性能时，不加-s
即使通过slave进行升级，也推荐使用该方式升级，速度比较快

MySQL参数设置

参数分类

• 全局参数：GLOBAL
  • 可修改参数
  • 不可修改参数

• 会话参数：SESSION
  • 可修改参数
  • 不可修改参数

1: 用户可在线修改非只读参数，只读参数只能预先在配置文件中进行设置，通过重启数据库实例,方可生效。

2: 所有的在线修改过的参数(GLOBAL/SESSION)，在重启后，都会丢失，不会写如my.cnf，无法将修改进行持久化

3: 有些参数，即存在于GLOBAL又存在于SESSION, 比如autocommit(PS：MySQL默认是提交的)

查看参数

mysql> show variables;

#  查看包含 log 的参数
mysql> show variables like '%log%';

参数设置

设置全局参数

mysql> set global slow_query_log = off; #不加global，会提示错误
                                        #slow_query_log是全局参数

mysql> set slow_query_log = off;  # 下面就报错了，默认是会话参数
ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBAL

设置session参数

mysql> set autocommit = 0;  # 当前会话生效

# 或者

mysql> set session autocommit = 0;  # 当前会话生效
autocommit同样在GLOBAL中, 也有同样的参数

mysql> set global autocommit = 1; #当前实例，全局生效

# 执行的效果如下：

mysql> show variables like "slow%"; # 原值为ON
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| slow_launch_time    | 2        |
| slow_query_log      | OFF      |
| slow_query_log_file | slow.log |
+---------------------+----------+
3 rows in set (0.00 sec)

mysql> select @@session.autocommit; # 等价于 slect @@autocomit;
+----------------------+
| @@session.autocommit |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.00 sec)

mysql> select @@global.autocommit;       
+---------------------+
| @@global.autocommit |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

注意：如果这个时候/etc/init.d/mysqld restart, 则全局的autocommit的值会变成默认值，或者依赖于my.cnf的设置值。

MySQL 用户管理

‘用户 + IP’概念

MySQL中同一个用户名，比如Bob,能否登录，以及用什么密码登录，可以访问什么库等等，都需要加上IP，才可以表示一个完整的用户标识

bob@127.0.0.1 和 bob@loalhost 以及 bob@192.168.1.100 这三个其实是不同的 用户标识

用户权限管理

系统表权限信息:

a)、查看mysql.user表【查看全局所有库的权限】

mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)

b)、查看mysql.db表 【查看指定库的权限】

mysql> desc db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(32)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)

c)、查看mysql.table_priv表 【查看指定表的权限】

mysql> desc tables_priv\G
*************************** 1. row ***************************
  Field: Host
   Type: char(60)
   Null: NO
    Key: PRI
Default: 
  Extra: 
*************************** 2. row ***************************
  Field: Db
   Type: char(64)
   Null: NO
    Key: PRI
Default: 
  Extra: 
*************************** 3. row ***************************
  Field: User
   Type: char(32)
   Null: NO
    Key: PRI
Default: 
  Extra: 
*************************** 4. row ***************************
  Field: Table_name
   Type: char(64)
   Null: NO
    Key: PRI
Default: 
  Extra: 
*************************** 5. row ***************************
  Field: Grantor
   Type: char(93)
   Null: NO
    Key: MUL
Default: 
  Extra: 
*************************** 6. row ***************************
  Field: Timestamp
   Type: timestamp
   Null: NO
    Key: 
Default: CURRENT_TIMESTAMP
  Extra: on update CURRENT_TIMESTAMP
*************************** 7. row ***************************
  Field: Table_priv
   Type: set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger')
   Null: NO
    Key: 
Default: 
  Extra: 
*************************** 8. row ***************************
  Field: Column_priv
   Type: set('Select','Insert','Update','References')
   Null: NO
    Key: 
Default: 
  Extra: 
8 rows in set (0.01 sec)

d)、查看mysql.column_priv表 【查看指定列的权限】

mysql> desc columns_priv;
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                                         | Null | Key | Default           | Extra                       |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Host        | char(60)                                     | NO   | PRI |                   |                             |
| Db          | char(64)                                     | NO   | PRI |                   |                             |
| User        | char(32)                                     | NO   | PRI |                   |                             |
| Table_name  | char(64)                                     | NO   | PRI |                   |                             |
| Column_name | char(64)                                     | NO   | PRI |                   |                             |
| Timestamp   | timestamp                                    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Column_priv | set('Select','Insert','Update','References') | NO   |     |                   |                             |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.00 sec)

权限

• 所有权限

image.png

• 常用权限

SQL语句：SELECT、INSERT、UPDATE、DELETE、INDEX
存储过程：CREATE ROUTINE、ALTER ROUTINE、EXECUTE、TRIGGER
管理权限：SUPER、RELOAD、SHOW DATABASE、SHUTDOWN、

• 显示当前用户权限

# 这三个是同一个意思
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for current_user;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for current_user();
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

创建用户权限

先创建用户create user '用户名'@'ip' identified by '密码'，然后给用户分配权限grant 权限 on 数据库.数据表 to '用户'@'ip'

mysql> create user 'chase'@'127.0.0.1' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select,insert on sys.* to 'chase'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

mysql> 

查看用户权限

mysql> show grants for 'chase'@'127.0.0.1';
+--------------------------------------------------------+
| Grants for chase@127.0.0.1                             |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'chase'@'127.0.0.1'              |
| GRANT SELECT, INSERT ON `sys`.* TO 'chase'@'127.0.0.1' |
+--------------------------------------------------------+
2 rows in set (0.00 sec)

USAGE表示用户可以登录，对sys所有表有SELECT, INSERT权限

撤销权限

• revoke 关键字，该关键字只删除用户权限，不删除用户
• revoke 语法同grant一致, 从grant ... to 变为revoke ... from

mysql> show grants for 'chase'@'127.0.0.1';
+--------------------------------------------------------+
| Grants for chase@127.0.0.1                             |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'chase'@'127.0.0.1'              |
| GRANT SELECT, INSERT ON `sys`.* TO 'chase'@'127.0.0.1' |
+--------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> revoke insert on sys.* from 'chase'@'127.0.0.1';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'chase'@'127.0.0.1';
+------------------------------------------------+
| Grants for chase@127.0.0.1                     |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'chase'@'127.0.0.1'      |
| GRANT SELECT ON `sys`.* TO 'chase'@'127.0.0.1' |
+------------------------------------------------+
2 rows in set (0.00 sec)

mysql> 

删除用户

drop user '用户'@'ip'

mysql> select host,user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| 127.0.0.1 | chase         |
| 127.0.0.1 | jim           |
| 127.0.0.1 | tom           |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
6 rows in set (0.00 sec)

mysql> drop user 'tom'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| 127.0.0.1 | chase         |
| 127.0.0.1 | jim           |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
5 rows in set (0.00 sec)

mysql> 

MySQL Utilities

下载：https://downloads.mysql.com/archives/get/file/mysql-utilities-1.6.5.tar.gz

cd /usr/local/src

wget https://downloads.mysql.com/archives/get/file/mysql-utilities-1.6.5.tar.gz

tar zxvf mysql-utilities-1.6.5.tar.gz

python setup.py install

安装成功后，在/usr/local/bin/目录下生成很多mysql...命令

查看.frm文件

[root@iZwz956snfyrvah6yq8sa4Z ~]# mysqlfrm --diagnostic /usr/local/mysql/data/test/aa.frm 
# WARNING: Cannot generate character set or collation names without the --server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for /usr/local/mysql/data/test/aa.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE `test`.`aa` (
  `id` int(11) NOT NULL AUTO_INCREMENT, 
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;

#...done.
[root@iZwz956snfyrvah6yq8sa4Z ~]# 

MySQL多实例安装

1. 多实例介绍

• 一台服务器上安装多个MySQL数据库实例
• 可以充分利用服务器的硬件资源
• 通过mysqld_multi进行管理

2. 安装要求

• MySQL实例1 - mysql1

  • port = 3306
  • datadir = /data1
  • socket = /tmp/mysql.sock1

• MySQL实例2 - mysql2

  • port = 3307
  • datadir = /data2
  • socket = /tmp/mysql.sock2

• MySQL实例3 - mysql3

  • port = 3308
  • datadir = /data3
  • socket = /tmp/mysql.sock3

• MySQL实例4 - mysql4

  • port = 3309
  • datadir = /data4
  • socket = /tmp/mysql.sock4

该三个参数必须定制，且必须不同 (port / datadir / socket)
server-id和多数据库实例没有关系，和数据库复制有关系

3. 安装操作

#
# 多实例配置文件，可以mysqld_multi --example 查看例子
#
[root@MyServer /]> cat /etc/my.cnf 
#[client]           # 这个标签如果配置了用户和密码，
                    # 并且[mysqld_multi]下没有配置用户名密码，
                    # 则mysqld_multi stop时, 会使用这个密码
                    # 如果没有精确的匹配，则匹配[client]标签
#user = root        
#password = 123
#-------------
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = multi_admin
pass = 123  # 官方文档中写的password，但是存在bug，需要改成pass(v5.7.9)
            # 写成password，start时正常，stop时，报如下错误
            # Access denied for user 'multi_admin'@'localhost' (using password: YES)
log = /var/log/mysqld_multi.log


[mysqld1]  # mysqld后面的数字为GNR, 是该实例的标识
           # mysqld_multi  start 1,  mysqld_multi start 2-4
server-id = 11
socket = /tmp/mysql.sock1
port = 3306
bind_address = 0.0.0.0
datadir = /data1
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
skip_name_resolve = 1
log_error = error.log
pid-file = /data1/mysql.pid1


[mysqld2]
server-id = 12
socket = /tmp/mysql.sock2
port = 3307
bind_address = 0.0.0.0
datadir = /data2
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
skip_name_resolve = 1
log_error = error.log
pid-file = /data2/mysql.pid2


[mysqld3]
server-id = 13
socket = /tmp/mysql.sock3
port = 3308
bind_address = 0.0.0.0
datadir = /data3
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
skip_name_resolve = 1
log_error = error.log
pid-file = /data3/mysql.pid3


[mysqld4]
server-id = 14
socket = /tmp/mysql.sock4
port = 3309
bind_address = 0.0.0.0
datadir = /data4
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
skip_name_resolve = 1
log_error = error.log
pid-file = /data4/mysql.pid4
#
# 准备好数据目录，并初始化安装
#
[root@MyServer ~]> mkdir /data1
[root@MyServer ~]> mkdir /data2
[root@MyServer ~]> mkdir /data3
[root@MyServer ~]> mkdir /data4
[root@MyServer ~]> chown mysql.mysql /data{1..4}
[root@MyServer ~]> mysqld --initialize --user=mysql --datadir=/data1
#
# 一些日志输出，并提示临时密码，下同
#
[root@MyServer ~]> mysqld --initialize --user=mysql --datadir=/data2
[root@MyServer ~]> mysqld --initialize --user=mysql --datadir=/data3
[root@MyServer ~]> mysqld --initialize --user=mysql --datadir=/data4
# 安装后，需要检查error.log 确保没有错误出现
[root@MyServer ~]> cp /usr/local/mysql/support-files/mysqld_multi.server  /etc/init.d/mysqld_multid 
# 拷贝启动脚本，方便自启
[root@MyServer ~]> chkconfig mysqld_multid on
[root@MyServer ~]> mysqld_multi  start
[root@MyServer ~]> mysqld_multi  report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running
[root@MyServer ~]> netstat -tunlp | grep mysql
[root@MyServer ~]> netstat -tunlp | grep mysql
tcp        0      0 :::3307                     :::*                        LISTEN      6221/mysqld         
tcp        0      0 :::3308                     :::*                        LISTEN      6232/mysqld         
tcp        0      0 :::3309                     :::*                        LISTEN      6238/mysqld         
tcp        0      0 :::3306                     :::*                        LISTEN      6201/mysqld         

[root@MyServer ~]> mysql -u root -S /tmp/mysql.sock1 -p -P3306
#
# 使用-S /tmp/mysql.sock1 进行登录，并输入临时密码后，修改密码，下同
#
[root@MyServer ~]> mysql -u root -S /tmp/mysql.sock2 -p -P3307
[root@MyServer ~]> mysql -u root -S /tmp/mysql.sock3 -p -P3308
[root@MyServer ~]> mysql -u root -S /tmp/mysql.sock4 -p -P3309
--
-- mysql1
--
mysql> show variables like "port"; 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like "socket";
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| socket        | /tmp/mysql.sock1 |
+---------------+------------------+
1 row in set (0.01 sec)

mysql> show variables like "datadir";
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| datadir       | /data1/ |
+---------------+---------+
1 row in set (0.00 sec)

--
-- 这样才能进行关闭数据库的操作
-- 和[mysqld_multi]中的user，pass(注意在5.7.9中不是password)对应起来 （类比[client]标签）
-- 一会测试federated链接，需要增加federated参数，并重启mysql2
--
mysql> create user 'multi_admin'@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> grant shutdown on *.* to 'multi_admin'@'localhost';

--
-- mysql2, mysql3, mysql4 类似。可以看到与my.cnf中对应的port和socket
--