mysql用法总结

 

rpm用法：

安装 mysql-5.0.22-2.1.0.1.i386.rpm
# rpm -ivh mysql/*.rpm

启动mysql服务：
# service mysqld start

查看mysql是否正常监听3306端口：
# netstat -nat
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN

设置mysql默认用户root的密码：
# mysqladmin -u root -p password '123456'

以root用户登陆mysql数据库：
# mysql -u root -p

备份数据库：
# mysqldump -u root -p --opt test>test.bak

导入备份：
# mysql -u root -p test<test.bak

远程登陆mysql：
# mysql -u root -p -h 192.168.1.27
#########################################################################################
mysql官方tar.gz用法：

添加mysql系统用户：
# groupadd mysql
# useradd -r -g mysql mysql
-r参数说明是系统用户，不可登录。系统用户没有用户之家目录，若非要用户之家目录，则再加-m参数。

解压mysql-5.5.16-linux2.6-i686.tar.gz：
# cd /home/mysql
# tar -zxvf mysql-5.5.16-linux2.6-i686.tar.gz

# cd /home/mysql/mysql-5.5.16-linux2.6-i686
# chown -R mysql:mysql .

初始化数据库（以mysql用户身份生成权限表）：
# scripts/mysql_install_db --user=mysql
该步骤必须在mysql目录中。

# chown -R root .
# chown -R mysql data
权限设置的最终结果为，除了data文件夹属于mysql用户外，其它文件所有者都为root。这样保证了mysql用户只有对数据库操作的权限。

# ln -s /home/mysql/mysql-5.5.16-linux2.6-i686 /usr/local/mysql

启动mysql：
# /usr/local/mysql/support-files/mysql.server start
推荐，但必须软链接到/usr/local/mysql。

# bin/mysqld_safe --user=mysql &
该步骤必须在mysql目录中执行，且停止mysql时要输入密码。

停止mysql：
# /usr/local/mysql/support-files/mysql.server stop
# /usr/local/mysql/bin/mysqladmin -u root -p shutdown

可选步骤：
# ln -s /home/mysql/mysql-5.5.16-linux2.6-i686/support-files/my-medium.cnf /etc/my.cnf
my.cnf是mysql的配置文件。

注册mysql服务：
# ln -s /home/mysql/mysql-5.5.16-linux2.6-i686/support-files/mysql.server /etc/rc.d/init.d/mysql.server

开机启用mysql服务：
# chkconfig --level 35 mysql.server on
#######################################################################################
我的用法：

解压zxvf mysql-5.5.16-linux2.6-i686.tar.gz：
$ cd /home/mysql
$ tar -zxvf mysql-5.5.16-linux2.6-i686.tar.gz

初始化数据库：
$ cd /home/mysql/mysql-5.5.16-linux2.6-i686
$ scripts/mysql_install_db

设置mysql目录：
$ vi /home/mysql/mysql-5.5.16-linux2.6-i686/support-files/mysql.server
将第46行改为：basedir=/home/mysql/mysql-5.5.16-linux2.6-i686

启动mysql：
$ /home/mysql/mysql-5.5.16-linux2.6-i686/support-files/mysql.server start

设置root账户密码：
$ /home/mysql/mysql-5.5.16-linux2.6-i686/bin/mysqladmin -u root -p password '123456'

以root账户登录mysql：
$ /home/mysql/mysql-5.5.16-linux2.6-i686/bin/mysql -u root -p

停止mysql：
$ /home/mysql/mysql-5.5.16-linux2.6-i686/support-files/mysql.server stop

创建数据库：
mysql> create database newdb;

导入脚本：
mysql> source /tmp/script.sql;

删除数据库：
mysql> drop database if exists newdb;

##################################################

-h参数无法连接远程mysql的问题：
登录mysql，查询mysql数据库的user表

mysql> use mysql;
mysql> select * from user;

+-----------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host                  | User | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+-----------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| localhost             | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
| localhost.localdomain | root |                                           | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
| 127.0.0.1             | root |                                           | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
| ::1                   | root |                                           | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
| localhost             |      |                                           | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 |        | NULL                  |
| localhost.localdomain |      |                                           | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 |        | NULL                  |
+-----------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+

发现只有localhost有Password，手动更新Password：
mysql> UPDATE user SET Password=PASSWORD('123456') where USER='root';
mysql> select * from user;

+-----------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host                  | User | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+-----------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| localhost             | root | *4587F84DF9FD2B70D6642A68A8E62647F92C0519 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
| localhost.localdomain | root | *4587F84DF9FD2B70D6642A68A8E62647F92C0519 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
| 127.0.0.1             | root | *4587F84DF9FD2B70D6642A68A8E62647F92C0519 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
| ::1                   | root | *4587F84DF9FD2B70D6642A68A8E62647F92C0519 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
| localhost             |      |                                           | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 |        | NULL                  |
| localhost.localdomain |      |                                           | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 |        | NULL                  |
+-----------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
6 rows in set (0.00 sec)

就可以使用-h参数了。可见mysql与主机名息息相关。