MySQL and MariaDB memo

MariaDB

第一次安裝完成，設定root的密碼

mysqladmin -u root -p ‘密碼’ or mysql_secure_installation

重設root密碼

/etc/init.d/mysql stop
mysqld_safe --skip-grant-tables &
mysql -u root
mysql> use mysql;
mysql> UPDATE user SET Password=PASSWORD("密碼") WHERE User='root';
mysql> flush privileges;
mysql> quit
/etc/init.d/mysql stop
/etc/init.d/mysql start

連線管理資料庫

mysqladmin -u root -p
Enter password:  此時再輸入密碼(建議採用)

修改使用者密碼
方法一
使用有權限或要修改的使用者本身登入mysql
mysql> SET PASSWORD FOR '目標使用者'@'主機' = PASSWORD('密碼');
mysql> flush privileges

方法二
使用有權限的使用者登入mysql
修改使用者密碼，只改 root 的密碼，如果沒有用 where ，則表示改全部 user 的密碼
mysql> use mysql;
mysql> UPDATE user SET password=password('密碼') where user='root';
mysql> FLUSH PRIVILEGES;

上面是不分主機位址的修改，若要像方法一區分主機的話再加上Host條件，例如
mysql> UPDATE user SET Password=PASSWORD("密碼") WHERE User='root' AND Host = 'localhost';
mysql> FLUSH PRIVILEGES;

方法三
同樣利用mysqladmin指令可以修改root或其他使用者密碼，但該使用者必須有SUPER權限
mysqladmin -u 使用者 -p'舊密碼' password '新密碼'

忘記密碼重設
/etc/init.d/mysql stop
mysqld_safe --skip-grant-tables &
用上面方式啟動mysql後可以不用輸入密碼直接連入
mysql -u root
接者使用修改使用者密碼的方法二修改root密碼，最後重新啟動mysql

新增root可遠端存取 %表示任何IP或只接輸入IP

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '密碼' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

資料庫(DateBase)十五種權限
ALL PRIVILEGES、ALTER、CREATE、DELETE、DROP、FILE、INDEX、INSERT、PROCESS、REFERENCES、RELOAD、SELECT、SHUTDOWN、UPDATE、USAGE

資料表(Table)八種權限
SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、INDEX、ALTER

資料欄(column)三種權限
SELECT INSERT UPDATE

查詢現有User

select user from mysql.user;

查詢MySQL 對 此帳號 開放(GRANT)哪些權限

查詢 某 User 的權限

SELECT User,Host FROM mysql.user; # 秀出系統現在有哪些 user
SHOW GRANTS FOR username@localhost; # 會秀出開此 username 時下的 Grant 語法, 也可用此來做帳號備份.
結果: GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'username'@'localhost' IDENTIFIED BY PASSWORD
結果: GRANT USAGE ON *.* TO '帳號'@'192.168.88.%' IDENTIFIED BY PASSWORD
'             GRANT SELECT, EXECUTE ON '資料庫'.* TO '帳號'@'192.168.88.%'

下述這些結果都一樣, 都是列出 目前此User 的權限.

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

修改

mysql> update db set Host='202.54.10.20' where Db='webdb';
mysql> update user set Host='202.54.10.20' where user='webadmin';

刪除

mysql> DELETE FROM mysql.user WHERE User = 'root' AND Host = '%';
mysql> FLUSH PRIVILEGES;

刪除空帳號

mysql> DELETE FROM user WHERE User = '';
mysql> FLUSH PRIVILEGES;

建立新帳號

GRANT SELECT,INSERT,UPDATE ON datab_name.* TO user@host IDENTIFIED BY 'passwd';
GRANT ALL ON *.* TO root@'10.99.1.%' IDENTIFIED BY '密碼';

mysql> GRANT 權限 ON 資料庫或資料表 TO 使用者 IDENTIFIED BY '密碼';
資料庫或資料表
*.*所有資料庫裡的所有資料表
*預設資料庫裡的所有資料表
資料庫.*某一資料庫裡的所有資料表
資料庫.資料表某一資料庫裡的特定資料表
資料表預設資料庫裡的某一資料表

範例 把 db35 這個資料庫(含其下的所有資料表)，授權給 s35，從 localhost 上來

mysql> GRANT all ON bugdb.* TO bug@'localhost' IDENTIFIED BY '密碼';
mysql -h host -u user -p

安全性設定(*.*為資料庫.資料表, @前面的*表示帳號, %可改成IP比如140.92.25.1)

GRANT ALL PRIVILEGES ON *.* TO '*'@'%' IDENTIFIED BY '密碼' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;   （最後一定要強迫更新權限）

Permissible Privileges for GRANT and REVOKE
Privilege    Meaning
ALL [PRIVILEGES]    Grant all privileges at specified access level except GRANT OPTION
ALTER    Enable use of ALTER TABLE
ALTER ROUTINE    Enable stored routines to be altered or dropped
CREATE    Enable database and table creation
CREATE ROUTINE    Enable stored routine creation
CREATE TEMPORARY TABLES    Enable use of CREATE TEMPORARY TABLE
CREATE USER    Enable use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES
CREATE VIEW    Enable views to be created or altered
DELETE    Enable use of DELETE
DROP    Enable databases, tables, and views to be dropped
EVENT    Enable use of events for the Event Scheduler
EXECUTE    Enable the user to execute stored routines
FILE    Enable the user to cause the server to read or write files
GRANT OPTION    Enable privileges to be granted to or removed from other accounts
INDEX    Enable indexes to be created or dropped
INSERT    Enable use of INSERT
LOCK TABLES    Enable use of LOCK TABLES on tables for which you have the SELECT privilege
PROCESS    Enable the user to see all processes with SHOW PROCESSLIST
REFERENCES    Not implemented
RELOAD    Enable use of FLUSH operations
REPLICATION CLIENT    Enable the user to ask where master or slave servers are
REPLICATION SLAVE    Enable replication slaves to read binary log events from the master
SELECT    Enable use of SELECT
SHOW DATABASES    Enable SHOW DATABASES to show all databases
SHOW VIEW    Enable use of SHOW CREATE VIEW
SHUTDOWN    Enable use of mysqladmin shutdown
SUPER    Enable use of other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug command
TRIGGER    Enable trigger operations
UPDATE    Enable use of UPDATE
USAGE    Synonym for “no privileges”

顯示目前有幾個資料庫

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bugdb              |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

列出該資料庫所有資料表名稱

mysql> SHOW TABLES FROM 資料庫名 [LIKE ...];

列出該資料表所有欄位名稱

mysql> SHOW COLUMNS FROM table_name [LIKE ...];
mysql> SHOW COLUMNS FROM table_name FROM db_name  [LIKE ...];
mysql> SHOW FIELDS FROM table_name [LIKE ...];
mysql> DESCRIBE table_name ;
mysql> EXPLAIN table_name ;

查詢資料庫大小

SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema;
+--------------------+---------------+
| DB Name            | DB Size in MB |
+--------------------+---------------+
| bugdb              |           2.5 |
| information_schema |           0.0 |
| mysql              |           0.6 |
+--------------------+---------------+

查詢MySQL VARIABLES

mysql> SHOW VARIABLES LIKE '%log_bin%';
+---------------------------------+-------------------------------------------------+
| Variable_name                   | Value                                           |
+---------------------------------+-------------------------------------------------+
| log_bin                         | ON                                              |
| log_bin_basename                | /rdsdbdata/log/binlog/mysql-bin-changelog       |
| log_bin_index                   | /rdsdbdata/log/binlog/mysql-bin-changelog.index |
| log_bin_trust_function_creators | ON                                              |
| log_bin_use_v1_row_events       | OFF                                             |
| sql_log_bin                     | ON                                              |
+---------------------------------+-------------------------------------------------+

建立資料庫;

CREATE DATABASE 資料庫名;

使用資料庫

USE 資料庫名;

刪除資料庫 DROP DATABASE 資料庫名;

DROP DATABASE [IF EXISTS] 資料庫名;

建立資料表 CREATE TABLE 資料表名 (欄位1 資料型態, 欄位2 資料型態, ……);

CREATE TABLE 資料表名 (autono INT NOT NULL AUTO_INCREMENT PRIMARY KEY, RACKID varchar(10), RACKLEVEL varchar(10), KVMID varchar(10), CUSERY varchar(20), SERVERENAME varchar(50), SERVERCNAME varchar(50), SERVERTYPE varchar(50), OSTYPE varchar(50), IPADDRESS varchar(20), SERVICEINFO varchar(50), CPUTYPE varchar(50), RAM varchar(20), STORAGE varchar(20), CRID varchar(50), FNTYPE varchar(20));

資料結構(type):
資料型態     說明
TINYINT     有符號的範圍是-128到127， 無符號的範圍是0到255。
SMALLINT     有符號的範圍是-32768到32767， 無符號的範圍是0到65535。
MEDIUMINT     有符號的範圍是-8388608到8388607， 無符號的範圍是0到16777215。
INT     有符號的範圍是-2147483648到2147483647， 無符號的範圍是0到4294967295。
INTEGER     INT的同義詞。
BIGINT     有符號的範圍是-9223372036854775808到 9223372036854775807，無符號的範圍是0到18446744073709551615。
FLOAT     單精密浮點數字。不能無符號。允許的值是-3.402823466E+38到- 1.175494351E-38，0 和1.175494351E-38到3.402823466E+38。
DOUBLE     雙精密)浮點數字。不能無符號。允許的值是- 1.7976931348623157E+308到-2.2250738585072014E-308、 0和2.2250738585072014E-308到1.7976931348623157E+308。
DOUBLE PRECISION     DOUBLE的同義詞。
REAL     DOUBLE的同義詞。
DECIMAL     DECIMAL值的最大範圍與DOUBLE相 同。
NUMERIC     DECIMAL的同義詞。
DATE     日期。支援的範圍是'1000-01-01'到'9999-12-31'。
DATETIME     日期和時間組合。支援的範圍是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'
TIMESTAMP     時間戳記。範圍是'1970-01-01 00:00:00'到2037年的某時。
TIME     一個時間。範圍是'-838:59:59'到'838:59:59'。
YEAR     2或4位數字格式的年(內定是4位)。允許的值是1901到2155。
CHAR     固定長度，1 ～ 255個字元。
VARCHAR     可變長度，1 ～ 255個字元。

TINYBLOB

TINYTEXT     最大長度為255(2^8-1)個字符。

MEDIUMBLOB

MEDIUMTEXT     最大長度為16777215(2^24-1)個字符。

LONGBLOB

LONGTEXT     最大長度為4294967295(2^32-1)個字符。
ENUM     一個ENUM最多能有65535不同的值。
SET     一個SET最多能有64個成員。

顯示表格

SHOW TABLES;

刪除資料表 DROP TABLE 資料表名;

DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...]

顯示表格結構

DESCRIBE infolist;

新增資料

INSERT INTO infolist (RACKID,RACKLEVEL,KVMID,CUSERY,SERVERENAME,SERVERCNAME,SERVERTYPE,OSTYPE,IPADDRESS,SERVICEINFO,CPUTYPE,RAM,STORAGE,CRID,FNTYPE) VALUES ('1','1','1','test','demo center','DynaManager-90','ASUS RS500-E6','CAKE v3.0.16 Alpha Final','140.92.25.6','Virtualization(DAS)','Intel(R) Xeon(R) CPU E5620  @ 2.40GH *2','24G','DAS 1TB','S/N:134IH11','DeSSerT');

Using System Variables

SHOW VARIABLES;
SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';
SHOW VARIABLES LIKE 'connect_timeout';

備份某個資料庫

mysqldump -u root -p -h 主機 資料庫名 > 資料庫備份檔名

備份all資料庫

mysqldump -u root -p -h 主機 --all-databases

同時備份多個MySQL資料庫

mysqldump -u root -p -h 主機 –databases 資料庫名1 資料庫名2 資料庫名3 > 資料庫備份檔名

備份MySQL資料庫為帶刪除表的格式,能夠讓該備份覆蓋已有資料庫而不需要手動刪除原有資料庫.

mysqldump -h 主機 -u root -p -–add-drop-table 資料庫名 > 資料庫備份檔名

僅備份資料庫結構

mysqldump -h 主機 -u root -p --no-data -–databases databasename1 databasename2 databasename3 > structurebackupfile.sql

備份MySQL資料庫某個(些)表

mysqldump -h 主機 -u root -p 資料庫名 specific_table1 specific_table2 > backupfile.sql

刪除資料庫的所有TABLES(DROP remove tables)

mysql -h 主機 -u root -p -Nse 'show tables' 資料庫名 | while read table; do mysql -u root -p -e "drop table $table" 資料庫名; done

清空資料庫的所有TABLES(Truncate empty tables)

mysql -h 主機 -u root -p -Nse 'SHOW TABLES' 資料庫名 | while read table; do mysql -u root -p -e "truncate table $table" 資料庫名; done

復原一個資料庫 (需先建好資料庫)

mysql -u root -p -h 主機 資料庫名 < 資料庫備份檔

還原壓縮的MySQL資料庫

gunzip < 資料庫備份檔名.sql.gz | mysql -h 主機 -u root -p 資料庫名

將資料庫轉移到新伺服器

mysqldump -u -p databasename | mysql –host=*.*.*.* -C databasename

If you want to see only a specific variable, you can use this command. Obviously you’d want to replace the max_connect_errors in that command with the variable that you’re looking for.

SHOW VARIABLES LIKE '%max_connect_errors%';

If you want to change the current state of a variable, you can do so easily with a command similar to this one:

SET GLOBAL max_connect_errors=10000;
Mysql> FLUSH HOSTS;

mysqlbinlog –start-datetime="2016-03-01 00:00:00" –stop-datetime="2016-03-16 00:00:00" -d bid mysql-bin.0001* > /root/replay.sql

tips

You need to use the -p flag to send a password. And it's tricky because you must have no space between -p and the password.

$ mysql -h "server-name" -u "root" "-pXXXXXXXX" "database-name" < "filename.sql"

If you use a space after -p it makes the mysql client prompt you interactively for the password, and then it interprets the next command argument as a database-name:

$ mysql -h "server-name" -u "root" -p "XXXXXXXX" "database-name" < "filename.sql"
Enter password:
ERROR 1049 (42000): Unknown database 'XXXXXXXX'

To avoid password prompt just create ~/.my.cnf file as follows:

[client]
#for local server use localhost
#host=localhost
host=10.0.1.100
user=vivek
password=myPassword

[mysql]
pager=/usr/bin/less

Then:

$ mysql -h "server-name" "database-name" < "filename.sql"

slow query

查看Server設定 ：show variables ;

其中兩個參數，一個是slow_launch_time，一個是long_query_time。
slow_launch_time跟slow query log沒有任何關係, 它代表的是thread create的一個門檻值，long_query_time才是正確的。

查看Server運作的各種設定 ： show global status;

可以用 show variables like '%slow%';過濾要找的值。

測試slow query，可以在用select搭sleep指令：
select sleep(2);

然後再去看slow query log