Debian & Ubuntu 安装 MySQL#
- 更新 apt-get 包管理sudo apt-get update
- 安装Mysql，安装完成后默认启动sudo apt-get -y install mysql-server
- 提高安全性：如果在安装过程中你未配置root用户密码，可以使用mysql_secure_installation 来设置密码sudo mysql_secure_installation
连接MySQL#
- 使用mysql命令行连接mysqlsudo mysql -u root -p
- 查询进程列表，包括当前的连接show processlist; +----+------+-----------+-------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+-------+---------+------+----------+------------------+ | 5 | root | localhost | mysql | Query | 0 | starting | show processlist | +----+------+-----------+-------+---------+------+----------+------------------+ 1 row in set (0.00 sec)
- 查询用户列表select user, host from user; +------------------+-----------+ | user | host | +------------------+-----------+ | debian-sys-maint | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +------------------+-----------+ 4 rows in set (0.00 sec)
- 创建用户create user test@localhost identified by ‘password’
- 创建用户并授权指定database，此时用户指可以操作所有数据库grant all on *.* to test@localhost identified by 'password'
- 创建用户并授权指定 test database, 此时用户指可以操作test数据库grant all on test.* to test@localhost identified by 'password'
- 授予外网登录权限, 此时test用户可以通过外网访问到内部数据库grant all privileges on test.* to test@‘%‘ identified by 'password'
配置MySQL#
Ubuntu下mysql的配置目录在 /etc/mysql. 此目录下所有的文件列表如下：
/etc/mysql$ ls -al
total 32
drwxr-xr-x 4 root root 4096 Dec 10 02:01 .
drwxr-xr-x 102 root root 4096 Dec 10 02:29 ..
drwxr-xr-x 2 root root 4096 Dec 10 02:00 conf.d
-rwxr-xr-x 1 root root 120 Oct 23 12:09 debian-start
-rw------- 1 root root 317 Dec 10 02:01 debian.cnf
lrwxrwxrwx 1 root root 24 Dec 10 02:00 my.cnf -> /etc/alternatives/my.cnf
-rw-r--r-- 1 root root 839 Jan 21 2017 my.cnf.fallback
-rw-r--r-- 1 root root 682 Feb 3 2017 mysql.cnf
drwxr-xr-x 2 root root 4096 Dec 10 02:01 mysql.conf.dmysql 的配置文件为 mysql.conf.d/mysqld.cnf, 所以当你要修改配置时，只需要更改此文件即可。
- 修改mysql 服务器编码[mysqld] character-set-server = utf8 collation-server = utf8_unicode_ci
- 修改监服务器监听地址，默认数据库只监听本地地址，外网无法访问，将监听地址修改为0.0.0.0即可[mysqld] bind-address = 127.0.0.1
- 忽略大小写[mysqld] lower_case_table_names = 0 #存储比较大小写敏感 lower_case_table_names = 1 #存储在硬盘上使用小写，比较忽略大小写 lower_case_table_names = 2 #存储时使用传入的值，比较时使用小写
- 导出数据库mysqldump -u username -p *old_dbname* > dbexport.sql
- 导入数据库mysql -u username -p *new_dbname* < dbexport.sql
- 导出数据库中指定的表, 如导出数据库db中的表 t1, t2, t3mysqldump -u username -p db t1 t2 t3 > mydb_tables.sql
启动停止#
在执行任何配置变更时，请先 停止mysql， 配置文件修改完成后再启动mysql
sudo systemctl stop mysql.service //停止
sudo systemctl start mysql.service //启动查看修改后的配置信息
show global variables like '%character%';
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+
show global variables like '%lower_%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 1 |
+------------------------+-------+重置root密码#
如果忘记root密码，可以使用如下步骤来重置root密码
- 停止Mysql Server
- mysqld_safe --skip-grant-tables; 使用如上命令，以非安全方式启动mysql
- 打开另一个终端，在终端中执行如下命令即可重置密码
update user set authentication_string=PASSWORD("password") where user="root";
flush privileges;