在Ubuntu中安装MySQL
程序员文章站
2022-03-28 12:06:37
...
前言 安装MySQL是个老话题，我安装MySQL服务器已不下百次了，为了博客文章结构的连贯性，还是再写一篇做为环境基础，同时也给自己一个备忘。 目录 MySQL在 Linux Ubuntu 中安装 通过命令行客户端访问MySQL 修改MySQL服务器的配置 新建数据库并设置访问账号
前言
安装MySQL是个老话题，我安装MySQL服务器已不下百次了，为了博客文章结构的连贯性，还是再写一篇做为环境基础，同时也给自己一个备忘。
目录
- MySQL在Linux Ubuntu中安装
- 通过命令行客户端访问MySQL
- 修改MySQL服务器的配置
- 新建数据库并设置访问账号
- 改变数据存储位置
1. MySQL在Linux Ubuntu中安装
本文使用的Linux是Ubuntu 12.04.2 LTS 64bit的系统，安装MySQL数据库软件包可以通过apt-get实现。
在Linux Ubuntu中安装MySQL数据库
#安装MySQL服务器端
~ sudo apt-get install mysql-server
安装过程会弹出提示框，输入root用户的密码，我在这里设置密码为mysql。
安装完成后，MySQL服务器会自动启动，我们检查MySQL服务器程序
# 检查MySQL服务器系统进程
~ ps -aux|grep mysql
mysql 3205 2.0 0.5 549896 44092 ? Ssl 20:10 0:00 /usr/sbin/mysqld
conan 3360 0.0 0.0 11064 928 pts/0 S+ 20:10 0:00 grep --color=auto mysql
# 检查MySQL服务器占用端口
~ netstat -nlt|grep 3306
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN
# 通过启动命令检查MySQL服务器状态
~ sudo /etc/init.d/mysql status
mysql start/running, process 3205
# 通过系统服务命令检查MySQL服务器状态
~ service mysql status
mysql start/running, process 3205
2. 通过命令行客户端访问MySQL
安装MySQL服务器，会自动地一起安装MySQL命令行客户端程序。
在本机输入mysql命令就可以启动，客户端程序访问MySQL服务器。
~ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 5.5.35-0ubuntu0.12.04.2 (Ubuntu)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
使用户名和密码，登陆服务器
~ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.5.35-0ubuntu0.12.04.2 (Ubuntu)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
MySQL的一些简单的命令操作。
# 查看所有的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)
# 切换到information_schema库
mysql> use information_schema
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
# 查看information_schema库中所有的表
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLESPACES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| INNODB_BUFFER_PAGE |
| INNODB_TRX |
| INNODB_BUFFER_POOL_STATS |
| INNODB_LOCK_WAITS |
| INNODB_CMPMEM |
| INNODB_CMP |
| INNODB_LOCKS |
| INNODB_CMPMEM_RESET |
| INNODB_CMP_RESET |
| INNODB_BUFFER_PAGE_LRU |
+---------------------------------------+
40 rows in set (0.01 sec)
# 查看数据库的字符集编码
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
3. 修改MySQL服务器的配置
接下来，我需要做一些配置，让MySQL符合基本的开发要求。
4.1 将字符编码设置为UTF-8
默认情况下，MySQL的字符集是latin1，因此在存储中文的时候，会出现乱码的情况，所以我们需要把字符集统一改成UTF-8。
用vi打开MySQL服务器的配置文件my.cnf
4.2 让MySQL服务器被远程访问
默认情况下，MySQL服务器不允许远程访问，只允许本机访问，所以我们需要设置打开远程访问的功能。
用vi打开MySQL服务器的配置文件my.cnf
~ sudo vi /etc/mysql/my.cnf
#注释bind-address
#bind-address = 127.0.0.1
修改后，重启MySQL服务器。
~ sudo /etc/init.d/mysql restart
Rather than invoking init scripts through /etc/init.d, use the service(8)
utility, e.g. service mysql restart
Since the script you are attempting to invoke has been converted to an
Upstart job, you may also use the stop(8) and then start(8) utilities,
e.g. stop mysql ; start mysql. The restart(8) utility is also available.
mysql start/running, process 3577
重新登陆服务器
~ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.5.35-0ubuntu0.12.04.2 (Ubuntu)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# 再次查看字符串编码
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
我们检查MySQL的网络监听端口
# 检查MySQL服务器占用端口
~ netstat -nlt|grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
我们看到从之间的网络监听从 127.0.0.1:3306 变成 0 0.0.0.0:3306，表示MySQL已经允许远程登陆访问。通过root账号远程访问，是非常不安全的操作，因此我们下一步，将新建一个数据库，再新建一个用户进行远程访问。
4. 新建数据库并设置访问账号
通过root账号登陆MySQl服务器
~ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 5.5.35-0ubuntu0.12.04.2 (Ubuntu)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# 新建数据库abc
mysql> CREATE DATABASE abc;
# 使用数据库abc
mysql> use abc;
Database changed
# 在数据库abc中，新建一张表a1
mysql> create table a1(id int primary key,name varchar(32) not null);
Query OK, 0 rows affected (0.05 sec)
# 新建book用户，密码为book，允许book可以远程访问abc数据库，授权book对abc进行所有数据库
mysql> GRANT ALL ON abc.* to book@'%' IDENTIFIED BY 'book';
Query OK, 0 rows affected (0.00 sec)
#允许book可以本地访问abc数据库，授权book对abc进行所有数据库
mysql> GRANT ALL ON abc.* to book@localhost IDENTIFIED BY 'book';
Query OK, 0 rows affected (0.00 sec)
我们在本地使用book用户登陆
# 使用book用户登陆
~ mysql -ubook -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.5.35-0ubuntu0.12.04.2 (Ubuntu)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#进行abc数据库
mysql> use abc;
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
#查看abc数据库的表
mysql> show tables;
+---------------+
| Tables_in_abc |
+---------------+
| a1 |
+---------------+
1 row in set (0.00 sec)
我们在远程的另一台Linux使用book用户登陆
~ mysql -ubook -p -h 192.168.1.199
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 5.5.35-0ubuntu0.12.04.2 (Ubuntu)
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use abc
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_abc |
+---------------+
| a1 |
+---------------+
1 row in set (0.00 sec)
5. 改变数据存储位置
有时候我们可能还需要改变MySQL数据存储的位置，一种方法是直接修改配置文件 /etc/mysql/my.cnf，找到datadir属性修改目录。
~ vi /etc/mysql/my.cnf
[mysqld]
datadir = /var/lib/mysql
如果通过这种方法修改，那么其他的调用存储路径的地方，我们也都需要进行修改，比如 用到了/usr/bin/mysql_install_db 命令，文件中ldata的属性也需要修改，关于mysql_install_db 命令的使用可以参考文章，[MySQL优化]为MySQL数据文件ibdata1瘦身。
还有另一种修改存储位置的方法，就是通过Linux系统的软连(ln -s)接来做的。当我们新挂载一块硬盘，停止MySQL服务，然后把/var/lib/mysql目录移动到新的硬盘存储，在/var/lib/mysql处建立指定新位置的软连接就行了。
# 停止MySQL服务器
~ /etc/init.d/mysql stop
# 挂载硬盘
~ mount -t ext4 /dev/vdb1 /vdb1
# 建立新存储目录
~ mkdir /vdb1/data
# 移动MySQL数据目录到新目录
~ mv /var/lib/mysql /vdb1/data/
# 软连接
~ ln -s /vdb1/data/mysql /var/lib/mysql
修改apparmor的别名定义文件
~ vi /etc/apparmor.d/tunables/alias
alias /var/lib/mysql/ -> /vdb1/data/mysql/,
注：如果没有修改apparmor的配置，MySQL会启动不了，并一直提示是权限的问题。
# 重启apparmor服务
~ /etc/init.d/apparmor restart
# 重启MySQL服务器
~ /etc/init.d/mysql start
这样就完成了，MySQL数据存储位置修改。
通过上面的操作，我们就把MySQL数据库服务器，在Linux Ubuntu中的系统安装完成。