MySQL 使用基础

安装 MySQL

注：以 Ubuntu 12.04 LTS 为例

安装 MySQL 服务器：

~$ sudo apt-get install mysql-server

只安装 MySQL 客户端：

~$ sudo apt-get install mysql-client

修改 root 密码

$ mysqladmin -u root -p password <new_passwd>
Enter password:<old_passwd>

启动或关闭 MySQL

$ sudo service mysqld start|stop
$ sudo /etc/rc.d/init.d/mysqld start|stop

查看 MySQL 版本

2.2 Determining Your Current MySQL Version

登陆时会有版本信息显示

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 50
Server version: 5.5.37-0ubuntu0.12.04.1 (Ubuntu)

mysql> SHOW VARIABLES LIKE "%version%";

mysql> STATUS;

注意：

mysql --version(-V) 这种方法是错误的，因为这是查看 mysql 这个程序的版本，而非 MySQL server(mysqld) 的版本。

查看所有用户

mysql> SELECT User,Host,Password FROM user;
+------------------+----------------------+-------------------------------------------+
| User             | Host                 | Password                                  |
+------------------+----------------------+-------------------------------------------+
| root             | localhost            | *15943532B0D808E5C8710C3C76D9FB509BC41666 |
| root             | controller.openstack | *15943532B0D808E5C8710C3C76D9FB509BC41666 |
| root             | 127.0.0.1            | *15943532B0D808E5C8710C3C76D9FB509BC41666 |
| root             | ::1                  | *15943532B0D808E5C8710C3C76D9FB509BC41666 |
| debian-sys-maint | localhost            | *48EAED3CA6FCD5B1672404BBC7FEC70C8F911856 |
| keystone         | %                    | *AD5CC56971C9CD6CB2822CEE3608405EF7A791E6 |
| keystone         | localhost            | *AD5CC56971C9CD6CB2822CEE3608405EF7A791E6 |
+------------------+----------------------+-------------------------------------------+
7 rows in set (0.00 sec)

连接 MySQL server

--host=host_name, -h host_name
--port=port_num, -P port_num
--user=user_name, -u user_name
--database=db_name, -D db_name
--password[=password], -p[password]

MySQL server 所在服务器：controller.openstack 192.168.0.11

另一装有 mysql 客户端的主机：compute.openstack 192.168.0.12

能匹配到 mysql.user 中记录的 '<user>'@'<host>' 组合才能成功连接

例一：本地登陆

~$ mysql -u root -h 192.168.0.11 -p        
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'192.168.0.11' (using password: YES)

'root'@'192.168.0.11' 不存在，所以连接失败

例二：远程登陆，在 192.168.0.12 中登陆

~$ mysql -u keystone -h controller.openstack -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
......

这种方式连接时， MySQL server 识别的组合是 'keystone'@'compute.openstack', 它匹配 'keystone'@'%' 组合

解决：以允许 root 远程和用 ip 本地登陆为例

mysql> GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY '<password_for_root>';

这里涉及到 MySQL 的权限管理，了解更多，参见 

6.2 The MySQL Access Privilege System

phpMyAdmin 修改数据库登录用户名和密码

phpMyAdmin 安装目录下的 config.inc.php：

$cfg['Servers'][$i]['user'] = '<db_user>';
$cfg['Servers'][$i]['password'] = '<passwd>';

创建用户、授权、删除用户、回收权限

首先要理解 MySQL “用户”的概念，用户并非单纯的 mysql.user 里的 User 列，完整的用户概念应该是 '<user>'@'<host>'.

创建用户、删除用户：

CREATE USER 'test'@'%' IDENTIFIED BY 'testpw';
DROP USER 'test'@'%';

授权：

GRANT ALL ON keystone.* TO 'keystone'@'localhost' IDENTIFIED BY 'hwx@keystone';

如果 keystone 用户不存在，这条语句会创建用户。

回收权限：

回收权限最好不要用 REVOKE, 因为它不会删除相关表中的记录（如 mysql.user, mysql,db），用 DELETE 有不能保证清理干净，所以用 DROP USER:

DROP USER 'keystone'@'localhost';

更多参见：

13.7.1. Account Management Statements

6.2 The MySQL Access Privilege System

导出整个库结构

mysqldump --no-data <db> [table1 table2 ...] -h <host> -u <user> -p >/tmp/dump.sql
sed -i 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' /tmp/dump.sql

--no-data: 只导出结构，不导出数据

--databases: 同时指定多个 db, 而且会生成 "CREATE DATABASE"  和 "USE" 语句

执行整个 sql 文件

mysql -h <host> -u <user> -p </tmp/dump.sql

查看建表建库语句

SHOW CREATE DATABASE `<db>`\G
SHOW CREATE TABLE `<db>`\G

查看表使用的存储引擎

SHOW TABLE STATUS FROM <db> where Name=<table>;

其中 "Engine" 字段就是存储引擎，需要注意的是通过 "SHOW CREATE TABLE" 查到的可能会不准确（

查看mysql当前表使用的存储引擎）。

将某值为时间戳的字段转换为可读格式

FROM_UNIXTIME(field)

查看 MySQL 服务器当前默认字符集和指定数据库的字符集

查看 MySQL 服务器当前默认字符集：

mysql> SHOW VARIABLES LIKE 'character_set_server';
mysql> SHOW VARIABLES LIKE 'collation_server';

查看指定数据库的字符集：

mysql> use somedb
mysql> SHOW VARIABLES LIKE 'character_set_database';
mysql> SHOW VARIABLES LIKE 'collation_database';

使用事务

mysql_query("START TRANSACTION");   // mysql_query("BEGIN");
$res1 = mysql_query("<SQL1>");
$res2 = mysql_query("<SQL2>");
if($res1 && $res2)
{
    mysql_query("COMMIT");
}
else
{
    mysql_query("ROLLBACK");
}