Mysql 安裝與配置县匠、用戶管理

Linux安裝

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

image.png

下載完成后狞尔，為安全性，校驗(yàn)安裝包

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

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

對(duì)比官方的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

創(chuàng)建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 ...

創(chuàng)建mysql軟連接

cd /usr/local

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

刪除鏈接莲兢，可使用unlink或rm

創(chuàng)建mysql-files文件汹来，并給當(dāng)前目錄下所有文件分配mysql所屬用戶和所屬組

cd /usr/local

mkdir mysql-files

chown -R root:mysql .

chown -R mysql:mysql mysql-files

chmod 750 mysql-files

MySQL配置讀取規(guī)則：優(yōu)先從右往左讀取
/etc/my.cnf 《== /etc/mysql/my.cnf 《== /usr/local/mysql/etc/my.cnf 《== ~/.my.cnf
查看讀取規(guī)則：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的線上最優(yōu)配置，8.0的可根據(jù)error日志報(bào)錯(cuò)提示來修改

初始化mysql

cd /usr/local

bin/mysqld --initialize --user=mysql

注：因?yàn)樯厦媾渲弥械膌og怒见、innodb文件設(shè)置的值比較大俗慈，初始化時(shí)間有點(diǎn)久，如果出現(xiàn)內(nèi)存不足遣耍，可相對(duì)應(yīng)的調(diào)整設(shè)置值小點(diǎn)
查看磁盤io：io -xm 3

設(shè)置datadir所屬用戶和所屬組為mysql

cd /data

chown -R mysql:mysql

啟動(dòng)mysql

bin/mysql_ssl_rsa_setup

bin/mysqld_safe --user=mysql &

查看mysqld啟動(dòng)狀態(tài)

ps -ef | grep mysqld

設(shè)置mysql安全啟動(dòng)方式和開機(jī)自啟動(dòng)

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

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

將```mysql````配置到系統(tǒng)環(huán)境變量中

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開始闺阱，安裝完成后會(huì)分配一個(gè)臨時(shí)的初始化密碼，用戶一定要重置初始化root密碼舵变，初始化臨時(shí)密碼在log文件里可以查看酣溃，從5.7開始，設(shè)置的密碼纪隙，password慢慢取消赊豌，使用md5()

初始化MySQL的root用戶密碼

mysql -u root -p
//輸入密碼

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

到此，MySQL安裝結(jié)束绵咱。

MySQL登錄

1碘饼、幾種登錄方式

方法一：該方法默認(rèn)使用root用戶, 可使用select user();查看當(dāng)前用戶

mysql -p

方法二：該方法適用于在安裝MySQL主機(jī)上進(jìn)行本地登錄

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

方法三：使用'root'@'127.0.0.1'這個(gè)用戶登錄

mysql -h 127.0.0.1 -u root -p

方法四：該方式等價(jià)與【方式二】，且和【方式三】屬于兩個(gè)不同的“用戶”

mysql -h localhost -u root -p

2悲伶、免密碼登錄

方法一：配置my.cnf

統(tǒng)一配置艾恼，增加【client】

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

單對(duì)定義不同的客戶端，這個(gè)是給/usr/loca/mysql/bin/mysql 使用的

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

這個(gè)是給/usr/local/mysql/bin/mysqladmin使用的

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

方法二：`login-path`該方式相對(duì)安全麸锉。如果server被黑了钠绍，該二進(jìn)制文件還是會(huì)被破解

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 # 這樣登錄就不需要密碼，且文件二進(jìn)制存儲(chǔ) ,位置是 ~/.mylogin.cnf

方法三：```~/.my.cnf````, 自己當(dāng)前家目錄

vim ~/.my.cnf

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

MySQL升級(jí)

安全關(guān)閉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

安全啟動(dòng)mysql

/etc/init.d/mysqld start

mysql_upgrade -p -s

參數(shù) -s 一定要加,表示只更新系統(tǒng)表柳爽，-s: upgrade-system-tables
如果不加-s,則會(huì)把所有庫的表以new mysql的方式重建媳握，線上千萬別這樣操作
因?yàn)閿?shù)據(jù)庫二進(jìn)制文件是兼容的，無需升級(jí)

什么時(shí)候不需要-s ? 當(dāng)一些老的版本的存儲(chǔ)格式需要新的特性磷脯，來提升性能時(shí)蛾找，不加-s
即使通過slave進(jìn)行升級(jí)，也推薦使用該方式升級(jí)争拐，速度比較快

MySQL參數(shù)設(shè)置

參數(shù)分類

全局參數(shù)：GLOBAL
- 可修改參數(shù)
- 不可修改參數(shù)

會(huì)話參數(shù)：SESSION
- 可修改參數(shù)
- 不可修改參數(shù)

1: 用戶可在線修改非只讀參數(shù)腋粥，只讀參數(shù)只能預(yù)先在配置文件中進(jìn)行設(shè)置，通過重啟數(shù)據(jù)庫實(shí)例,方可生效架曹。

2: 所有的在線修改過的參數(shù)(GLOBAL/SESSION)隘冲，在重啟后，都會(huì)丟失绑雄，不會(huì)寫如my.cnf展辞，無法將修改進(jìn)行持久化

3: 有些參數(shù)，即存在于GLOBAL又存在于SESSION, 比如autocommit(PS：MySQL默認(rèn)是提交的)

查看參數(shù)

mysql> show variables;

#  查看包含 log 的參數(shù)
mysql> show variables like '%log%';

參數(shù)設(shè)置

設(shè)置全局參數(shù)

mysql> set global slow_query_log = off; #不加global万牺，會(huì)提示錯(cuò)誤
                                        #slow_query_log是全局參數(shù)

mysql> set slow_query_log = off;  # 下面就報(bào)錯(cuò)了罗珍，默認(rèn)是會(huì)話參數(shù)
ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBAL

設(shè)置session參數(shù)

mysql> set autocommit = 0;  # 當(dāng)前會(huì)話生效

# 或者

mysql> set session autocommit = 0;  # 當(dāng)前會(huì)話生效
autocommit同樣在GLOBAL中, 也有同樣的參數(shù)

mysql> set global autocommit = 1; #當(dāng)前實(shí)例，全局生效

# 執(zhí)行的效果如下：

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; # 等價(jià)于 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)

注意：如果這個(gè)時(shí)候/etc/init.d/mysqld restart, 則全局的autocommit的值會(huì)變成默認(rèn)值脚粟，或者依賴于my.cnf的設(shè)置值覆旱。

MySQL 用戶管理

‘用戶 + IP’概念

MySQL中同一個(gè)用戶名，比如Bob,能否登錄核无，以及用什么密碼登錄扣唱，可以訪問什么庫等等，都需要加上IP团南，才可以表示一個(gè)完整的用戶標(biāo)識(shí)

bob@127.0.0.1 和 bob@loalhost 以及 bob@192.168.1.100 這三個(gè)其實(shí)是不同的用戶標(biāo)識(shí)

用戶權(quán)限管理

系統(tǒng)表權(quán)限信息:

a)噪沙、查看mysql.user表【查看全局所有庫的權(quán)限】

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表【查看指定庫的權(quán)限】

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表【查看指定表的權(quán)限】

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表【查看指定列的權(quán)限】

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)

權(quán)限

所有權(quán)限

image.png

常用權(quán)限

SQL語句：SELECT、INSERT拷橘、UPDATE局义、DELETE、INDEX
存儲(chǔ)過程：CREATE ROUTINE冗疮、ALTER ROUTINE萄唇、EXECUTE、TRIGGER
管理權(quán)限：SUPER赌厅、RELOAD穷绵、SHOW DATABASE轿塔、SHUTDOWN特愿、

顯示當(dāng)前用戶權(quán)限

# 這三個(gè)是同一個(gè)意思
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)

創(chuàng)建用戶權(quán)限

先創(chuàng)建用戶create user '用戶名'@'ip' identified by '密碼'仲墨，然后給用戶分配權(quán)限grant 權(quán)限 on 數(shù)據(jù)庫.數(shù)據(jù)表 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>

查看用戶權(quán)限

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表示用戶可以登錄，對(duì)sys所有表有SELECT, INSERT權(quán)限

撤銷權(quán)限

revoke 關(guān)鍵字揍障，該關(guān)鍵字只刪除用戶權(quán)限目养，不刪除用戶
revoke 語法同grant一致, 從grant ... to 變?yōu)?code>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多實(shí)例安裝

1. 多實(shí)例介紹

一臺(tái)服務(wù)器上安裝多個(gè)MySQL數(shù)據(jù)庫實(shí)例
可以充分利用服務(wù)器的硬件資源
通過mysqld_multi進(jìn)行管理

安裝要求

MySQL實(shí)例1 - mysql1
- port = 3306
- datadir = /data1
- socket = /tmp/mysql.sock1
MySQL實(shí)例2 - mysql2
- port = 3307
- datadir = /data2
- socket = /tmp/mysql.sock2
MySQL實(shí)例3 - mysql3
- port = 3308
- datadir = /data3
- socket = /tmp/mysql.sock3
MySQL實(shí)例4 - mysql4
- port = 3309
- datadir = /data4
- socket = /tmp/mysql.sock4

該三個(gè)參數(shù)必須定制毒嫡，且必須不同 (port / datadir / socket)
server-id和多數(shù)據(jù)庫實(shí)例沒有關(guān)系癌蚁，和數(shù)據(jù)庫復(fù)制有關(guān)系

3. 安裝操作

#
# 多實(shí)例配置文件，可以mysqld_multi --example 查看例子
#
[root@MyServer /]> cat /etc/my.cnf 
#[client]           # 這個(gè)標(biāo)簽如果配置了用戶和密碼兜畸，
                    # 并且[mysqld_multi]下沒有配置用戶名密碼努释，
                    # 則mysqld_multi stop時(shí), 會(huì)使用這個(gè)密碼
                    # 如果沒有精確的匹配，則匹配[client]標(biāo)簽
#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時(shí)正常肛鹏，stop時(shí)逸邦，報(bào)如下錯(cuò)誤
            # Access denied for user 'multi_admin'@'localhost' (using password: YES)
log = /var/log/mysqld_multi.log


[mysqld1]  # mysqld后面的數(shù)字為GNR, 是該實(shí)例的標(biāo)識(shí)
           # 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
#
# 準(zhǔn)備好數(shù)據(jù)目錄，并初始化安裝
#
[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
#
# 一些日志輸出在扰，并提示臨時(shí)密碼缕减，下同
#
[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 確保沒有錯(cuò)誤出現(xiàn)
[root@MyServer ~]> cp /usr/local/mysql/support-files/mysqld_multi.server  /etc/init.d/mysqld_multid 
# 拷貝啟動(dòng)腳本芒珠，方便自啟
[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 進(jìn)行登錄桥狡，并輸入臨時(shí)密碼后，修改密碼妓局，下同
#
[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)

--
-- 這樣才能進(jìn)行關(guān)閉數(shù)據(jù)庫的操作
-- 和[mysqld_multi]中的user总放，pass(注意在5.7.9中不是password)對(duì)應(yīng)起來 （類比[client]標(biāo)簽）
-- 一會(huì)測試federated鏈接，需要增加federated參數(shù)好爬，并重啟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中對(duì)應(yīng)的port和socket
--

最后編輯于：2019.05.25 02:12:46

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者

人面猴
序言：七十年代末存炮，一起剝皮案震驚了整個(gè)濱河市炬搭，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌穆桂，老刑警劉巖宫盔，帶你破解...
沈念sama閱讀 217,542評(píng)論 6贊 504
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異享完，居然都是意外死亡灼芭，警方通過查閱死者的電腦和手機(jī)，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 92,822評(píng)論 3贊 394
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門般又，熙熙樓的掌柜王于貴愁眉苦臉地迎上來彼绷，“玉大人巍佑，你說我怎么就攤上這事〖拿酰” “怎么了萤衰？”我有些...
開封第一講書人閱讀 163,912評(píng)論 0贊 354
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵，是天一觀的道長猜旬。經(jīng)常有香客問我脆栋，道長，這世上最難降的妖魔是什么洒擦？我笑而不...
開封第一講書人閱讀 58,449評(píng)論 1贊 293
?港島之戀（遺憾婚禮）
正文為了忘掉前任椿争，我火速辦了婚禮，結(jié)果婚禮上熟嫩，老公的妹妹穿的比我還像新娘丘薛。我一直安慰自己，他們只是感情好邦危，可當(dāng)我...
茶點(diǎn)故事閱讀 67,500評(píng)論 6贊 392
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布洋侨。她就那樣靜靜地躺著，像睡著了一般倦蚪。火紅的嫁衣襯著肌膚如雪希坚。梳的紋絲不亂的頭發(fā)上，一...
開封第一講書人閱讀 51,370評(píng)論 1贊 302
城市分裂傳說
那天陵且，我揣著相機(jī)與錄音裁僧，去河邊找鬼。笑死慕购，一個(gè)胖子當(dāng)著我的面吹牛聊疲，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播沪悲，決...
沈念sama閱讀 40,193評(píng)論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼获洲，長吁一口氣：“原來是場噩夢(mèng)啊……” “哼！你這毒婦竟也來了殿如？” 一聲冷哼從身側(cè)響起贡珊，我...
開封第一講書人閱讀 39,074評(píng)論 0贊 276
萬榮殺人案實(shí)錄
序言：老撾萬榮一對(duì)情侶失蹤，失蹤者是張志新（化名）和其女友劉穎涉馁，沒想到半個(gè)月后门岔，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
沈念sama閱讀 45,505評(píng)論 1贊 314
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡烤送，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 37,722評(píng)論 3贊 335
?白月光啟示錄
正文我和宋清朗相戀三年寒随，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
茶點(diǎn)故事閱讀 39,841評(píng)論 1贊 348
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡妻往，死狀恐怖逢防，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情蒲讯，我是刑警寧澤，帶...
沈念sama閱讀 35,569評(píng)論 5贊 345
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布灰署，位于F島的核電站判帮，受9級(jí)特大地震影響，放射性物質(zhì)發(fā)生泄漏溉箕。R本人自食惡果不足惜晦墙，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 41,168評(píng)論 3贊 328
男人毒藥：我在死后第九天來索命
文/蒙蒙一、第九天我趴在偏房一處隱蔽的房頂上張望肴茄。院中可真熱鬧晌畅，春花似錦、人聲如沸寡痰。這莊子的主人今日做“春日...
開封第一講書人閱讀 31,783評(píng)論 0贊 22
一樁弒父案，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽拦坠。三九已至连躏，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間贞滨，已是汗流浹背入热。一陣腳步聲響...
開封第一講書人閱讀 32,918評(píng)論 1贊 269
情欲美人皮
我被黑心中介騙來泰國打工，沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留晓铆，地道東北人勺良。一個(gè)月前我還...
沈念sama閱讀 47,962評(píng)論 2贊 370
代替公主和親
正文我出身青樓，卻偏偏與公主長得像骄噪，于是被迫代替她去往敵國和親尚困。傳聞我的和親對(duì)象是個(gè)殘疾皇子，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 44,781評(píng)論 2贊 354

Mysql 安裝與配置、用戶管理

Mysql 安裝與配置县匠、用戶管理

Linux安裝

MySQL登錄

1碘饼、幾種登錄方式

2悲伶、免密碼登錄

方法一：配置my.cnf

方法二：`login-path`該方式相對(duì)安全麸锉。如果server被黑了钠绍，該二進(jìn)制文件還是會(huì)被破解

方法三：```~/.my.cnf````, 自己當(dāng)前家目錄

MySQL升級(jí)

MySQL參數(shù)設(shè)置

參數(shù)分類

查看參數(shù)

參數(shù)設(shè)置

MySQL 用戶管理

‘用戶 + IP’概念

用戶權(quán)限管理

系統(tǒng)表權(quán)限信息:

權(quán)限

所有權(quán)限

常用權(quán)限

顯示當(dāng)前用戶權(quán)限

創(chuàng)建用戶權(quán)限

查看用戶權(quán)限

撤銷權(quán)限

刪除用戶

MySQL Utilities

MySQL多實(shí)例安裝

1. 多實(shí)例介紹

3. 安裝操作

推薦閱讀更多精彩內(nèi)容

Mysql 安裝與配置县匠、用戶管理

Linux安裝

MySQL登錄

1碘饼、幾種登錄方式

2悲伶、免密碼登錄

方法一：配置my.cnf

方法二：login-path該方式相對(duì)安全麸锉。如果server被黑了钠绍，該二進(jìn)制文件還是會(huì)被破解

方法三：```~/.my.cnf````, 自己當(dāng)前家目錄

MySQL升級(jí)

MySQL參數(shù)設(shè)置

參數(shù)分類

查看參數(shù)

參數(shù)設(shè)置

MySQL 用戶管理

‘用戶 + IP’概念

用戶權(quán)限管理

系統(tǒng)表權(quán)限信息:

權(quán)限

所有權(quán)限

常用權(quán)限

顯示當(dāng)前用戶權(quán)限

創(chuàng)建用戶權(quán)限

查看用戶權(quán)限

撤銷權(quán)限

刪除用戶

MySQL Utilities

MySQL多實(shí)例安裝

1. 多實(shí)例介紹

3. 安裝操作

推薦閱讀更多精彩內(nèi)容

方法二：`login-path`該方式相對(duì)安全麸锉。如果server被黑了钠绍，該二進(jìn)制文件還是會(huì)被破解