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
對比官方的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)錯提示來修改
初始化mysql
cd /usr/local
bin/mysqld --initialize --user=mysql
注：因?yàn)樯厦媾渲弥械膌og、innodb文件設(shè)置的值比較大，初始化時間有點(diǎn)久，如果出現(xiàn)內(nèi)存不足，可相對應(yīng)的調(diào)整設(shè)置值小點(diǎn)
查看磁盤io：io -xm 3
設(shè)置datadir所屬用戶和所屬組為mysql
cd /data
chown -R mysql:mysql
啟動mysql
bin/mysql_ssl_rsa_setup
bin/mysqld_safe --user=mysql &
查看mysqld啟動狀態(tài)
ps -ef | grep mysqld
設(shè)置mysql安全啟動方式和開機(jī)自啟動
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開始，安裝完成后會分配一個臨時的初始化密碼，用戶一定要重置初始化root密碼，初始化臨時密碼在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'這個用戶登錄
mysql -h 127.0.0.1 -u root -p
方法四：該方式等價與【方式二】，且和【方式三】屬于兩個不同的“用戶”
mysql -h localhost -u root -p
2、免密碼登錄
方法一：配置my.cnf
統(tǒng)一配置，增加【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被黑了，該二進(jìn)制文件還是會被破解
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)制存儲 ,位置是 ~/.mylogin.cnf
方法三：```~/.my.cnf````, 自己當(dāng)前家目錄
vim ~/.my.cnf
[client]
user = 'root'
password = '密碼'
MySQL升級
安全關(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
安全啟動mysql
/etc/init.d/mysqld start
mysql_upgrade -p -s
參數(shù) -s 一定要加,表示只更新系統(tǒng)表，-s: upgrade-system-tables
如果不加-s,則會把所有庫的表以new mysql的方式重建，線上千萬別這樣操作
因?yàn)閿?shù)據(jù)庫二進(jìn)制文件是兼容的，無需升級
什么時候不需要-s ? 當(dāng)一些老的版本的存儲格式需要新的特性，來提升性能時，不加-s
即使通過slave進(jìn)行升級，也推薦使用該方式升級，速度比較快
MySQL參數(shù)設(shè)置
參數(shù)分類
- 全局參數(shù)：
GLOBAL- 可修改參數(shù)
- 不可修改參數(shù)
- 會話參數(shù)：
SESSION- 可修改參數(shù)
- 不可修改參數(shù)
1: 用戶可在線修改非只讀參數(shù)，只讀參數(shù)只能預(yù)先在配置文件中進(jìn)行設(shè)置，通過重啟數(shù)據(jù)庫實(shí)例,方可生效。
2: 所有的在線修改過的參數(shù)(GLOBAL/SESSION)，在重啟后，都會丟失，不會寫如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，會提示錯誤
#slow_query_log是全局參數(shù)
mysql> set slow_query_log = off; # 下面就報(bào)錯了，默認(rèn)是會話參數(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)前會話生效
# 或者
mysql> set session autocommit = 0; # 當(dāng)前會話生效
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; # 等價于 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的值會變成默認(rèn)值，或者依賴于my.cnf的設(shè)置值。
MySQL 用戶管理
‘用戶 + IP’概念
MySQL中同一個用戶名，比如Bob,能否登錄，以及用什么密碼登錄，可以訪問什么庫等等，都需要加上IP，才可以表示一個完整的用戶標(biāo)識
bob@127.0.0.1 和 bob@loalhost 以及 bob@192.168.1.100 這三個其實(shí)是不同的 用戶標(biāo)識
用戶權(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)限
image.png
-
常用權(quán)限
SQL語句：SELECT、INSERT、UPDATE、DELETE、INDEX
存儲過程：CREATE ROUTINE、ALTER ROUTINE、EXECUTE、TRIGGER
管理權(quán)限：SUPER、RELOAD、SHOW DATABASE、SHUTDOWN、
-
顯示當(dāng)前用戶權(quán)限
# 這三個是同一個意思
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表示用戶可以登錄，對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í)例介紹
- 一臺服務(wù)器上安裝多個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
該三個參數(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] # 這個標(biāo)簽如果配置了用戶和密碼，
# 并且[mysqld_multi]下沒有配置用戶名密碼，
# 則mysqld_multi stop時, 會使用這個密碼
# 如果沒有精確的匹配，則匹配[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時正常，stop時，報(bào)如下錯誤
# Access denied for user 'multi_admin'@'localhost' (using password: YES)
log = /var/log/mysqld_multi.log
[mysqld1] # mysqld后面的數(shù)字為GNR, 是該實(shí)例的標(biāo)識
# 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
#
# 一些日志輸出，并提示臨時密碼，下同
#
[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 確保沒有錯誤出現(xiàn)
[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 進(jìn)行登錄，并輸入臨時密碼后，修改密碼，下同
#
[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)對應(yīng)起來 （類比[client]標(biāo)簽）
-- 一會測試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 類似?？梢钥吹脚cmy.cnf中對應(yīng)的port和socket
--
