sudo apt-get install mysql-server-5.0
Password:
正在读取软件包列表... 完成
正在分析软件包的依赖关系树
读取状态信息... 完成
将会安装下列额外的软件包：
libdbd-mysql-perl libdbi-perl libmysqlclient15off libnet-daemon-perl
libplrpc-perl mysql-client-5.0 mysql-common
建议安装的软件包：
dbishell libcompress-zlib-perl tinyca
推荐安装的软件包：
mailx
下列【新】软件包将被安装：
libdbd-mysql-perl libdbi-perl libmysqlclient15off libnet-daemon-perl
libplrpc-perl mysql-client-5.0 mysql-common mysql-server-5.0
共升级了 0 个软件包，新安装了 8 个软件包，要卸载 0 个软件包，有 187 个软件未被升级。
需要下载 35.9MB 的软件包。
解压缩后会消耗掉 93.6MB 的额外空间。
您希望继续执行吗？[Y/n]n
中止执行。
sudo apt-get install mysql-server
正在读取软件包列表... 完成
正在分析软件包的依赖关系树
读取状态信息... 完成
将会安装下列额外的软件包：
libdbd-mysql-perl libdbi-perl libmysqlclient15off libnet-daemon-perl
libplrpc-perl mysql-client-5.0 mysql-common mysql-server-5.0
建议安装的软件包：
dbishell libcompress-zlib-perl tinyca
推荐安装的软件包：
mailx
下列【新】软件包将被安装：
libdbd-mysql-perl libdbi-perl libmysqlclient15off libnet-daemon-perl
libplrpc-perl mysql-client-5.0 mysql-common mysql-server mysql-server-5.0
共升级了 0 个软件包，新安装了 9 个软件包，要卸载 0 个软件包，有 187 个软件未被升级。
需要下载 35.9MB 的软件包。
解压缩后会消耗掉 93.7MB 的额外空间。
您希望继续执行吗？[Y/n]y
获取：1 http://tw.archive.ubuntu.com feisty-security/main mysql-common 5.0.38-0ubuntu1.2 [54.8kB]
获取：2 http://tw.archive.ubuntu.com feisty/main libnet-daemon-perl 0.38-1.1 [45.9kB]
获取：3 http://tw.archive.ubuntu.com feisty/main libplrpc-perl 0.2017-1.1 [35.0kB]
获取：4 http://tw.archive.ubuntu.com feisty/main libdbi-perl 1.53-1build1 [648kB]
获取：5 http://tw.archive.ubuntu.com feisty-security/main libmysqlclient15off 5.0.38-0ubuntu1.2 [1835kB]
获取：6 http://tw.archive.ubuntu.com feisty/main libdbd-mysql-perl 3.0008-1build1 [140kB]
获取：7 http://tw.archive.ubuntu.com feisty-security/main mysql-client-5.0 5.0.38-0ubuntu1.2 [7362kB]
获取：8 http://tw.archive.ubuntu.com feisty-security/main mysql-server-5.0 5.0.38-0ubuntu1.2 [25.7MB]
获取：9 http://tw.archive.ubuntu.com feisty-security/main mysql-server 5.0.38-0ubuntu1.2 [48.2kB]
下载 35.9MB，耗时 7m16s (82.3kB/s)
正在预设定软件包 ...
选中了曾被取消选择的软件包 mysql-common。
(正在读取数据库 ... 系统当前总共安装有 96031 个文件和目录。)
正在解压缩 mysql-common (从 .../mysql-common_5.0.38-0ubuntu1.2_all.deb) ...
选中了曾被取消选择的软件包 libnet-daemon-perl。
正在解压缩 libnet-daemon-perl (从 .../libnet-daemon-perl_0.38-1.1_all.deb) ...
选中了曾被取消选择的软件包 libplrpc-perl。
正在解压缩 libplrpc-perl (从 .../libplrpc-perl_0.2017-1.1_all.deb) ...
选中了曾被取消选择的软件包 libdbi-perl。
正在解压缩 libdbi-perl (从 .../libdbi-perl_1.53-1build1_i386.deb) ...
选中了曾被取消选择的软件包 libmysqlclient15off。
正在解压缩 libmysqlclient15off (从 .../libmysqlclient15off_5.0.38-0ubuntu1.2_i386.deb) ...
选中了曾被取消选择的软件包 libdbd-mysql-perl。
正在解压缩 libdbd-mysql-perl (从 .../libdbd-mysql-perl_3.0008-1build1_i386.deb) ...
选中了曾被取消选择的软件包 mysql-client-5.0。
正在解压缩 mysql-client-5.0 (从 .../mysql-client-5.0_5.0.38-0ubuntu1.2_i386.deb) ...
正在设置 mysql-common (5.0.38-0ubuntu1.2) ...
选中了曾被取消选择的软件包 mysql-server-5.0。
(正在读取数据库 ... 系统当前总共安装有 96245 个文件和目录。)
正在解压缩 mysql-server-5.0 (从 .../mysql-server-5.0_5.0.38-0ubuntu1.2_i386.deb) ...
选中了曾被取消选择的软件包 mysql-server。
正在解压缩 mysql-server (从 .../mysql-server_5.0.38-0ubuntu1.2_all.deb) ...
正在设置 libnet-daemon-perl (0.38-1.1) ...
正在设置 libplrpc-perl (0.2017-1.1) ...
正在设置 libdbi-perl (1.53-1build1) ...
正在设置 libmysqlclient15off (5.0.38-0ubuntu1.2) ...
正在设置 libdbd-mysql-perl (3.0008-1build1) ...
正在设置 mysql-client-5.0 (5.0.38-0ubuntu1.2) ...
正在设置 mysql-server-5.0 (5.0.38-0ubuntu1.2) ...
* Stopping MySQL database server mysqld [ OK ]
* Starting MySQL database server mysqld [ OK ]
* Checking for corrupt, not cleanly closed and upgrade needing tables.
* Root password is blank. To change it use:
* /etc/init.d/mysql reset-password
正在设置 mysql-server (5.0.38-0ubuntu1.2) ...
2 设置密码：
mysql -h localhost -uroot -p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.0.38-Ubuntu_0ubuntu1.2-log Ubuntu 7.04 distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY "123456789";
Query OK, 0 rows affected (0.00 sec)
mysql> Aborted
3 改了密码之后：
:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.0.38-Ubuntu_0ubuntu1.2-log Ubuntu 7.04 distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
4 MySQL的常用操作
注意：MySQL中每个命令后都要以分号；结尾。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)
显示数据库中的表
mysql> use mysql (打开库，对每个库进行操作就要打开此库 )
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| func |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| proc |
| procs_priv |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
17 rows in set (0.00 sec)
显示数据表的结构
mysql> describe db;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
20 rows in set (0.00 sec)
显示表中的记录
mysql> select * from db;
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+
| Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv |
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+
| % | test | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | N | N |
| % | test\_% | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | N | N |
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+
2 rows in set (0.00 sec)
建库
mysql> create database wjl;
Query OK, 1 row affected (0.00 sec)
建表：
mysql> use wjl
Database changed
mysql> create table wjl(id int(3) auto_increment not null primary key, xm char(8),xb char(2),csny date);
Query OK, 0 rows affected (0.01 sec)
mysql> describe wjl;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| xm | char(8) | YES | | NULL | |
| xb | char(2) | YES | | NULL | |
| csny | date | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
增加记录
mysql> insert into wjl values('1','张三','男','1981-10-01');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from wjl;
+----+--------+------+------------+
| id | xm | xb | csny |
+----+--------+------+------------+
| 1 | 张三 | ?�?� | 1981-10-01 |
+----+--------+------+------------+
1 row in set (0.00 sec)
修改纪录
mysql> update wjl set csny='1999-9-01' where id='1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from wjl;
+----+--------+------+------------+
| id | xm | xb | csny |
+----+--------+------+------------+
| 1 | 张三 | ?�?� | 1999-09-01 |
+----+--------+------+------------+
1 row in set (0.00 sec)
增加MySQL用户
1 增加一个用户user_1密码为123，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入MySQL，然后键入以下命令：
grant select,insert,update,delete on *.* to user_1@"%" Identified by "123";
1增加的用户是十分危险的，如果知道了user_1的密码，那么他就可以在网上的任何一台电脑上登录你的MySQL数据库并对你的数据为所欲为了，解决办法见例2
2增加一个用户user_2密码为123,让此用户只可以在localhost上登录，并可以对数据库aaa进行查询、插入、修改、删除的操作(localhost指本地主机，即MySQL数据库所在的那台主机)，这样用户即使用知道user_2的密码，他也无法从网上直接访问数据库，只能通过 MYSQL主机来操作aaa库。
/usr/share/mysql目录中会有一个mysql的启动脚本mysql.server及示例配置文件等(如my-huge.cnf、 my-large.cnf、my-medium.cnf)rpm包安装完后自动将mysql安装成系统服务，所以可以使用下面命令启动、停止mysql. 有个实用程序/usr/bin/mysql_install_db，该程序可以用来初始化 mysql数据库，即创建/var/log/mysql目录，及创建mysql数据库(mysql授权表等信息)及test数据库(空库),如果不小心删除了/var/log/mysql目录可以通过该程序来初始化.
mysql>grant select,insert,update,delete on aaa.* to user_2@localhost identified by "123";
用新增的用户如果登录不了MySQL，在登录时用如下命令：
mysql -u user_1 -p -h 192.168.1.50 (-h后跟的是要登录主机的ip地址)
备份
数据放在：/var/lib/mysql
: 2）禁用root帐户
: sudo passwd -l root
1 启用root用户密码
: sudo passwd root
: 系统会提示你输入新密码，同时启用它。
su root
Password:
root@ja-desktop:/var/lib# cd mysql (进入到库目录)
root@ja-desktop:/var/lib/mysql# ls
debian-5.0.flag ib_logfile0 mysql wjl
ibdata1 ib_logfile1 mysql_upgrade_info
root@javawenjinlan-desktop:/var/lib/mysql# mysqldump -u root -p --opt wjl > back_wjl
Enter password:
删除纪录
root@ja-desktop:/var/lib/mysql# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.0.38-Ubuntu_0ubuntu1.2-log Ubuntu 7.04 distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| wjl |
+--------------------+
3 rows in set (0.00 sec)
mysql> use wjl
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
root@javawenjinlan-desktop:/var/lib/mysql# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.0.38-Ubuntu_0ubuntu1.2-log Ubuntu 7.04 distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| wjl |
+--------------------+
3 rows in set (0.00 sec)
mysql> use wjl
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
root@javawenjinlan-desktop:/var/lib/mysql# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.0.38-Ubuntu_0ubuntu1.2-log Ubuntu 7.04 distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| wjl |
+--------------------+
3 rows in set (0.00 sec)
mysql> use wjl
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------+
| Tables_in_wjl |
+---------------+
| wjl |
+---------------+
1 row in set (0.00 sec)
mysql> delete from wjl where id='1';
Query OK, 1 row affected (0.00 sec)
mysql> select * from wjl;
Empty set (0.00 sec)
删库和删表
mysql> drop table wjl
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| wjl |
+--------------------+
3 rows in set (0.00 sec)
mysql> drop database wjl
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)
恢复
root@ja-desktop:/var/lib/mysql# mysql -u root -p wjl < back_wjl
Enter password:
ERROR 1049 (42000): Unknown database 'wjl'
(我要把database wjl建立才可以)
mysql> create database wjl;
Query OK, 1 row affected (0.00 sec)
root@ja-desktop:/var/lib/mysql# mysql -u root -p wjl < back_wjl
Enter password:
root@ja-desktop:/var/lib/mysql#
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| wjl |
+--------------------+
3 rows in set (0.00 sec)
mysql> use wjl
Database changed
mysql> show tables;
+---------------+
| Tables_in_wjl |
+---------------+
| wjl |
+---------------+
1 row in set (0.00 sec)
mysql> select * from wjl;
+----+--------+------+------------+
| id | xm | xb | csny |
+----+--------+------+------------+
| 1 | 张三 | ?�?� | 1999-09-01 |
+----+--------+------+------------+
1 row in set (0.00 sec)
mysql>
可以看到database的恢复已经成功
