basic mysql configuration on ubuntu16.04

ubuntu16.04 default mysql

1
2
vi /etc/mysql/mysql.conf.d/mysqld.cnf
systemctl restart mysql

准許外部連線

1
#bind-address=127.0.0.1 #註解掉，許可外部連線。等同0.0.0.0

grant priviledges

1
2
3
4
5
6
7
8
9
10
select host,user from mysql.user;
GRANT ALL PRIVILEGES ON *.* TO 'leon'@'10.0.0.%'    IDENTIFIED BY 'asdfasdf;lkj'    WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'leon'@'192.168.1.%'    IDENTIFIED BY 'asdfasdf;lkj'    WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'leon'@'localhost'    IDENTIFIED BY 'asdfasdf;lkj'    WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'leon'@'leon-%'    IDENTIFIED BY 'asdfasdf;lkj'    WITH GRANT OPTION;
delete from mysql.user where host='::1';
delete from mysql.user where host='%';
commit; flush table mysql.user;
FLUSH PRIVILEGES;
select host,user from mysql.user;

import sql

1
mysql -uleon -paaaaaaaa target_database_name < db_dump.sql

dump sql

1
2
mysqldump --verbose -uleon -paaaaaaaa target_database_name > db_dump.sql
mysqldump --verbose -uleon -paaaaaaaa target_database_name specific_table > table_dump.sql

execute single sql

1
mysql --verbose -uleon -paaaaaaaa target_database_name -e "drop table if exists db1.table1; "

connect remote mysql server

1
mysql -uleon -paaaaaaaa -h 10.0.0.1

取消mysql大小寫敏感

1
2
[mysqld]
lower_case_table_names = 1

utf8 -> utf8mb4

show variables like ‘character%’;

1
2
3
4
5
6
7
8
9
10
11
[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'