安装

安装

shell> apt-get install mysql-server

查看版本号

shell> mysql --version

启动

shell> service mysql start

修改配置

允许所有来源的请求

shell> vim /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 0.0.0.0

• Bind address and MySQL server

修改 MySQL 字符集为 utf8mb4

shell> vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

• 10.5 Configuring Application Character Set and Collation

修改客户端连接字符集为 utf8mb4

shell> vim /etc/mysql/conf.d/mysql.cnf
[mysql]
default-character-set = utf8mb4

• 10.4 Connection Character Sets and Collations

查看当前字符集

mysql> show variables like "character%";

查看当前校对集

mysql> show variables like "collation%";

添加用户及授权

添加用户

mysql> CREATE USER 'username'@'172.16.%' IDENTIFIED BY 'password'

• 13.7.1.3 CREATE USER Syntax

对用户授权

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON `dbname`.* TO 'username'@'172.16.%'

• 13.7.1.4 GRANT Syntax

授予更多权限

mysql> GRANT CREATE ON `dbname`.* TO 'username'@'172.16.%'
mysql> GRANT SELECT ON `dbname2`.* TO 'username'@'172.16.%'

查看用户权限

mysql> SHOW GRANTS FOR 'username'@'172.16.%';

收回权限

mysql> REVOKE CREATE ON dbname.* FROM 'username'@'172.16.%';

• 13.7.1.6 REVOKE Syntax

技巧

避免插入主键或唯一健重复的记录（会提示警告而非致命错误）

mysql> INSERT IGNORE INTO `table_name` (`id`, `name`) VALUES ('1', 'suhua');

挂载数据目录到新的硬盘

shell> sudo chown -R mysql:mysql /data/mysql
shell> sudo chmod 755 /data/mysql
shell> vim /etc/apparmor.d/usr.sbin.mysqld
# Allow data dir access
  /data/mysql/ r,
  /data/mysql/** rwk,
shell> service mysql stop
shell> systemctl restart apparmor.service
shell> service mysql start

若发现切换数据目录后无法启动，可以关闭或卸载 apparmor

/etc/init.d/apparmor stop
/etc/init.d/apparmor teardown

# 卸载
update-rc.d -f apparmor remove
apt-get purge apparmor
reboot

• Can't create file /var/lib/mysql/user.lower-test