MySQL版本: Server version: 5.1.44 Source distribution
修改root密码
如果没有密码使用下面的命令, 将密码设为"123456"
$ mysqladmin -u root password 123456
如果有密码使用下面的命令, 将密码改为"123456"
$ mysqladmin -u root -p password 123456
Enter password:
用户账户管理
添加账户:
http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html#user-account-management
提到的方法不适用:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
根据http://dev.mysql.com/doc/refman/5.1/en/adding-users.html所说, 要先create user, 然后再使用grant, 根据尝试, 在mysql中, 语句不区分大小写. 正确的方法如下:
mysql> create user 'frank'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'frank'@'localhost' INDENTIFIED BY '123456' WITH GRANT OPTION;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INDENTIFIED BY '123456' WITH GRANT OPTION' at line 1
mysql> GRANT ALL PRIVILEGES ON *.* TO 'frank'@'localhost' WITH GRANT OPTION;Query OK, 0 rows affected (0.00 sec)
mysql>
以上语句创建了名为"frank"的用户, 密码为"123456", 该用户为超级用户, 只能从local host登录.
mysql> create user 'frank'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to 'frank'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql>
上面的语句创建了名为"frank"的超级用户, 密码为"123456", 该用户可以从任意主机登录. 创建localhost的"frank"用户是必须的:
It is necessary to have both accounts for monty to be able to connect from anywhere as monty . Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_db would take precedence when monty connects from the local host. As a result, monty would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'monty'@'%' account and thus comes earlier in the user table sort order. (user table sorting is discussed in Section 5.4.4, “Access Control, Stage 1: Connection Verification” .)
查看用户权限:
mysql> show grants for 'frank'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for frank@localhost |
+----------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'frank'@'localhost' IDENTIFIED BY PASSWORD '565491d704013245' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show grants for 'frank'@'%';
+--------------------------------------------------------------------------------------------------------+
| Grants for frank@% |
+--------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'frank'@'%' IDENTIFIED BY PASSWORD '565491d704013245' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
这样从理论上将应该就可以使用"frank"账户从远端登录到MySQL服务器了
在另外一台PC上输入命令:
$ mysql -h xxx.xxx.xxx.xxx -u frank -p
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' (113)
可能是由于防火墙的原因, 打开TCP端口3306, 重新链接, OK.
还可以使用insert的方法添加账户.
删除账户:
使用drop user来删除用户, 这里要注意@'%'和@'localhost'的账户要区分开来, 在create user时如果没有指定@'%'和@'localhost', 默认是@'%'
mysql> create user 'test1';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'test1';
+-----------------------------------+
| Grants for test1@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'%' |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> show grants for 'test1'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'test1' on host 'localhost'
mysql> show grants for 'test1'@'%';
+-----------------------------------+
| Grants for test1@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'%' |
+-----------------------------------+
1 row in set (0.00 sec)
mysql>
在使用"drop user"删除账户时, 如果没有指定@'%'或@'localhost', 默认也是@'%':
mysql> drop user 'test1';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'test1';
ERROR 1141 (42000): There is no such grant defined for user 'test1' on host '%'
mysql> show grants for 'test1'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'test1' on host '%'
mysql> show grants for 'test1'@'localhost';
+-------------------------------------------+
| Grants for test1@localhost |
+-------------------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'localhost' |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql>
如何查看数据库中有哪些账户?
http://pcedu.pconline.com.cn/empolder/db/mysql/0507/657144_1.html
显示命令
1、显示数据库列表。
show databases;
刚开始时才两个数据库：mysql和test。mysql库很重要它里面有MYSQL的系统信息，我们改密码和新增用户，实际上就是用这个库进行操作。
2、显示库中的数据表：
use mysql； ／／打开库，学过FOXBASE的一定不会陌生吧
show tables;
3、显示数据表的结构：
describe 表名;
4、建库：
create database 库名;
5、建表：
use 库名；
create table 表名 (字段设定列表)；
6、删库和删表:
drop database 库名;
drop table 表名；
7、将表中记录清空：
delete from 表名;
8、显示表中的记录：
select * from 表名;
备份与恢复
MySQL版本: Server version: 5.1.44 Source distribution
修改root密码
如果没有密码使用下面的命令, 将密码设为"123456"
$ mysqladmin -u root password 123456
如果有密码使用下面的命令, 将密码改为"123456"
$ mysqladmin -u root -p password 123456
Enter password:
用户账户管理
添加账户:
http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html#user-account-management
提到的方法不适用:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
根据http://dev.mysql.com/doc/refman/5.1/en/adding-users.html所说, 要先create user, 然后再使用grant, 根据尝试, 在mysql中, 语句不区分大小写. 正确的方法如下:
mysql> create user 'frank'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'frank'@'localhost' INDENTIFIED BY '123456' WITH GRANT OPTION;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INDENTIFIED BY '123456' WITH GRANT OPTION' at line 1
mysql> GRANT ALL PRIVILEGES ON *.* TO 'frank'@'localhost' WITH GRANT OPTION;Query OK, 0 rows affected (0.00 sec)
mysql>
以上语句创建了名为"frank"的用户, 密码为"123456", 该用户为超级用户, 只能从local host登录.
mysql> create user 'frank'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to 'frank'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql>
上面的语句创建了名为"frank"的超级用户, 密码为"123456", 该用户可以从任意主机登录. 创建localhost的"frank"用户是必须的:
It is necessary to have both accounts for monty to be able to connect from anywhere as monty . Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_db would take precedence when monty connects from the local host. As a result, monty would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'monty'@'%' account and thus comes earlier in the user table sort order. (user table sorting is discussed in Section 5.4.4, “Access Control, Stage 1: Connection Verification” .)
查看用户权限:
mysql> show grants for 'frank'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for frank@localhost |
+----------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'frank'@'localhost' IDENTIFIED BY PASSWORD '565491d704013245' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show grants for 'frank'@'%';
+--------------------------------------------------------------------------------------------------------+
| Grants for frank@% |
+--------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'frank'@'%' IDENTIFIED BY PASSWORD '565491d704013245' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
这样从理论上将应该就可以使用"frank"账户从远端登录到MySQL服务器了
在另外一台PC上输入命令:
$ mysql -h xxx.xxx.xxx.xxx -u frank -p
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' (113)
可能是由于防火墙的原因, 打开TCP端口3306, 重新链接, OK.
还可以使用insert的方法添加账户.
删除账户:
使用drop user来删除用户, 这里要注意@'%'和@'localhost'的账户要区分开来, 在create user时如果没有指定@'%'和@'localhost', 默认是@'%'
mysql> create user 'test1';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'test1';
+-----------------------------------+
| Grants for test1@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'%' |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> show grants for 'test1'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'test1' on host 'localhost'
mysql> show grants for 'test1'@'%';
+-----------------------------------+
| Grants for test1@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'%' |
+-----------------------------------+
1 row in set (0.00 sec)
mysql>
在使用"drop user"删除账户时, 如果没有指定@'%'或@'localhost', 默认也是@'%':
mysql> drop user 'test1';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'test1';
ERROR 1141 (42000): There is no such grant defined for user 'test1' on host '%'
mysql> show grants for 'test1'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'test1' on host '%'
mysql> show grants for 'test1'@'localhost';
+-------------------------------------------+
| Grants for test1@localhost |
+-------------------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'localhost' |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql>
如何查看数据库中有哪些账户?
http://pcedu.pconline.com.cn/empolder/db/mysql/0507/657144_1.html
显示命令
1、显示数据库列表。
show databases;
刚开始时才两个数据库：mysql和test。mysql库很重要它里面有MYSQL的系统信息，我们改密码和新增用户，实际上就是用这个库进行操作。
2、显示库中的数据表：
use mysql； ／／打开库，学过FOXBASE的一定不会陌生吧
show tables;
3、显示数据表的结构：
describe 表名;
4、建库：
create database 库名;
5、建表：
use 库名；
create table 表名 (字段设定列表)；
6、删库和删表:
drop database 库名;
drop table 表名；
7、将表中记录清空：
delete from 表名;
8、显示表中的记录：
select * from 表名;
备份与恢复
MySQL版本: Server version: 5.1.44 Source distribution
修改root密码
如果没有密码使用下面的命令, 将密码设为"123456"
$ mysqladmin -u root password 123456
如果有密码使用下面的命令, 将密码改为"123456"
$ mysqladmin -u root -p password 123456
Enter password:
用户账户管理
添加账户:
http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html#user-account-management
提到的方法不适用:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
根据http://dev.mysql.com/doc/refman/5.1/en/adding-users.html所说, 要先create user, 然后再使用grant, 根据尝试, 在mysql中, 语句不区分大小写. 正确的方法如下:
mysql> create user 'frank'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'frank'@'localhost' INDENTIFIED BY '123456' WITH GRANT OPTION;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INDENTIFIED BY '123456' WITH GRANT OPTION' at line 1
mysql> GRANT ALL PRIVILEGES ON *.* TO 'frank'@'localhost' WITH GRANT OPTION;Query OK, 0 rows affected (0.00 sec)
mysql>
以上语句创建了名为"frank"的用户, 密码为"123456", 该用户为超级用户, 只能从local host登录.
mysql> create user 'frank'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to 'frank'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql>
上面的语句创建了名为"frank"的超级用户, 密码为"123456", 该用户可以从任意主机登录. 创建localhost的"frank"用户是必须的:
It is necessary to have both accounts for monty to be able to connect from anywhere as monty . Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_db would take precedence when monty connects from the local host. As a result, monty would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'monty'@'%' account and thus comes earlier in the user table sort order. (user table sorting is discussed in Section 5.4.4, “Access Control, Stage 1: Connection Verification” .)
查看用户权限:
mysql> show grants for 'frank'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for frank@localhost |
+----------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'frank'@'localhost' IDENTIFIED BY PASSWORD '565491d704013245' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show grants for 'frank'@'%';
+--------------------------------------------------------------------------------------------------------+
| Grants for frank@% |
+--------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'frank'@'%' IDENTIFIED BY PASSWORD '565491d704013245' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
这样从理论上将应该就可以使用"frank"账户从远端登录到MySQL服务器了
在另外一台PC上输入命令:
$ mysql -h xxx.xxx.xxx.xxx -u frank -p
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' (113)
可能是由于防火墙的原因, 打开TCP端口3306, 重新链接, OK.
还可以使用insert的方法添加账户.
删除账户:
使用drop user来删除用户, 这里要注意@'%'和@'localhost'的账户要区分开来, 在create user时如果没有指定@'%'和@'localhost', 默认是@'%'
mysql> create user 'test1';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'test1';
+-----------------------------------+
| Grants for test1@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'%' |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> show grants for 'test1'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'test1' on host 'localhost'
mysql> show grants for 'test1'@'%';
+-----------------------------------+
| Grants for test1@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'%' |
+-----------------------------------+
1 row in set (0.00 sec)
mysql>
在使用"drop user"删除账户时, 如果没有指定@'%'或@'localhost', 默认也是@'%':
mysql> drop user 'test1';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'test1';
ERROR 1141 (42000): There is no such grant defined for user 'test1' on host '%'
mysql> show grants for 'test1'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'test1' on host '%'
mysql> show grants for 'test1'@'localhost';
+-------------------------------------------+
| Grants for test1@localhost |
+-------------------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'localhost' |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql>
如何查看数据库中有哪些账户?
A: 查看mysql数据库中的user表.
http://pcedu.pconline.com.cn/empolder/db/mysql/0507/657144_1.html
显示命令
1、显示数据库列表。
show databases;
刚开始时才两个数据库：mysql和test。mysql库很重要它里面有MYSQL的系统信息，我们改密码和新增用户，实际上就是用这个库进行操作。
2、显示库中的数据表：
use mysql； ／／打开库，学过FOXBASE的一定不会陌生吧
show tables;
3、显示数据表的结构：
describe 表名;
4、建库：
create database 库名;
5、建表：
use 库名；
create table 表名 (字段设定列表)；
6、删库和删表:
drop database 库名;
drop table 表名；
7、将表中记录清空：
delete from 表名;
8、显示表中的记录：
select * from 表名;
备份与恢复
<!-- @page { margin: 0.79in } P { margin-bottom: 0.08in } -->
$ mysqldump -u root –p[password] --opt BugFree > bugfree.sql
注意: -p和password之间没有空格.
<!-- @page { margin: 0.79in } P { margin-bottom: 0.08in } -->
$ mysqladmin create BugFree
$ mysql BugFree < bugfree.sql
Reference:
MySQL 5.1参考手册 http://dev.mysql.com/doc/refman/5.1/zh/index.html
MySQL 5.1 Reference Manual http://dev.mysql.com/doc/refman/5.1/en/index.html
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
