环境:腾讯云服务器(ubuntu 16.04.1)
背景:安装MySQL数据库,供个人开发使用
配置MySQL数据库,解决中文乱码,远程访问,数据包大小限制等问题
步骤:
- 使用xshell等工具登录至云服务器,切换至root用户:
ubuntu@VM-202-164-ubuntu:~$ su Password: root@VM-202-164-ubuntu:/home/ubuntu#
说明:笔者的主机开启了root登录,所以可以使用su命令切换至root,如果你的主机还不能使用su切换到root用户,那就需要初始化一下root用户的密码了,很简单,方法如下。
ubuntu@VM-202-164-ubuntu:~$ sudo passwd root Enter new UNIX password:
设置一个root密码,就可以su到root用户了。
xshell是一个终端模拟软件,类似于Windows的远程桌面,可以登录至远程主机,类似的软件还有很多。如果你使用的是虚拟机,直接登录就好了,不要拘泥于这些。
- 使用apt安装MySQL:
root@VM-202-164-ubuntu:/home/ubuntu# apt-get install mysql-server Reading package lists... Done Building dependency tree Reading state information... Done mysql-server is already the newest version (5.7.20-0ubuntu0.16.04.1). 0 upgraded, 0 newly installed, 0 to remove and 185 not upgraded. root@VM-202-164-ubuntu:/home/ubuntu#
说明:由于笔者的主机已经安装了最新版本的MySQL,所以会有上边的提示。注意:如果想要使用apt安装,请确保你的主机可以连接到互联网。此处会提示设置root用户的密码等,Y/N确认?等,正常输入就好。 - 到此MySQL服务器就安装成功了,下面我们验证一下是否安装成功,并做一些配置,免得产生不必要的麻烦:
查看进程:
root@VM-202-164-ubuntu:/home/ubuntu# ps -aux|grep mysql|grep -v grep mysql 27866 0.0 29.7 1148940 263592 ? Ssl Nov14 19:38 /usr/sbin/mysqld
说明:查看是否存在MySQL进程
查看端口:
root@VM-202-164-ubuntu:/home/ubuntu# netstat -nlt|grep 3306 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
说明:MySQL默认使用3306端口,查看该端口的状态
查看service状态:
root@VM-202-164-ubuntu:/home/ubuntu# service mysql status ● mysql.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled) Active: active (running) since Tue 2017-11-14 22:50:15 CST; 1 months 1 days ago Main PID: 27866 (mysqld) Tasks: 35 Memory: 263.1M CPU: 19min 39.122s CGroup: /system.slice/mysql.service └─27866 /usr/sbin/mysqld Warning: Journal has been rotated since unit was started. Log output is incomplete or unavailable. root@VM-202-164-ubuntu:/home/ubuntu#
说明:显示如上结果,说明service正常
通过启动命令检查MySQL服务器状态:
root@VM-202-164-ubuntu:/home/ubuntu# service mysql status ● mysql.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled) Active: active (running) since Tue 2017-11-14 22:50:15 CST; 1 months 1 days ago Main PID: 27866 (mysqld) Tasks: 35 Memory: 263.1M CPU: 19min 39.122s CGroup: /system.slice/mysql.service └─27866 /usr/sbin/mysqld Warning: Journal has been rotated since unit was started. Log output is incomplete or unavailable. root@VM-202-164-ubuntu:/home/ubuntu# /etc/init.d/mysql status ● mysql.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled) Active: active (running) since Tue 2017-11-14 22:50:15 CST; 1 months 1 days ago Main PID: 27866 (mysqld) Tasks: 35 Memory: 263.1M CPU: 19min 39.177s CGroup: /system.slice/mysql.service └─27866 /usr/sbin/mysqld Warning: Journal has been rotated since unit was started. Log output is incomplete or unavailable. root@VM-202-164-ubuntu:/home/ubuntu#
说明:显示如上结果,说明MySQL服务器状态正常。
到此MySQL数据库就可以正常使用了,可以试一下:
root@VM-202-164-ubuntu:/home/ubuntu# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 30257 Server version: 5.7.20-0ubuntu0.16.04.1 (Ubuntu) Copyright (c) 2000, 2017, 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 -u root -p ,输入刚刚设置的数据库root用户的密码(注意是数据库用户而不是系统用户哦),进入MySQL命令行界面,在Windows上用过MySQL的你对这个界面一定很熟悉了吧。写两个sql试试感觉:mysql> use mysql; Database changed mysql> select * from user; -- 一堆结果,不粘了,输入exit命令退出MySQL命令行 mysql>exit Bye
- 配置MySQL服务器的charset,此处解决MySQL插入中文乱码的情况。(该部分需要能够简单使用vi编辑器来修改配置文件)
root@VM-202-164-ubuntu:/home/ubuntu# cd /etc/mysql/mysql.conf.d root@VM-202-164-ubuntu:/etc/mysql/mysql.conf.d#
root@VM-202-164-ubuntu:/etc/mysql/mysql.conf.d# vi mysqld.cnf
#在[mysqld]标签下,增加服务器端的字符编码,增加[client]修改客户端编码 [mysqld] character-set-server=utf8 collation-server=utf8_general_ci [client] default-character-set=utf8
说明:修改mysqlld.cnf这个配置文件,此处针对MySQL 5.7.20举个栗子,各个版本的MySQL配置文件结构略有差异,总之就是被各种include,配置文件被各种拆分。但是需要修改的内容不变,这个问题应该难不倒聪明的你。修改完之后需要重启MySQL的,怎么重启呢,请看这↓。root@VM-202-164-ubuntu:~# /etc/init.d/mysql restart
最后,验证一下:mysql -u root -p mysql> show databases; mysql> use information_schema; mysql> show tables; mysql> show variables like '%char%';
依次执行上边这个↑,如果结果是下边这个↓,所有字符集都变成了utf-8,就搞定了,在进行一系列修改之前可并不是这样的呢。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.17 sec)
- 让MySQL可以被远程访问到。在MySQL的使用中,我们经常使用可视化界面的软件去连接数据库,比如SQLyog这个软件,但是配置好主机ip、用户名、密码等等等之后发现连接失败了,接失败了,失败了,败了,了~~~蛋疼得很,以下配置就是解决这个问题,开启MySQL服务器的远程访问。
root@VM-202-164-ubuntu:/home/ubuntu# cd /etc/mysql/mysql.conf.d root@VM-202-164-ubuntu:/etc/mysql/mysql.conf.d#
root@VM-202-164-ubuntu:/etc/mysql/mysql.conf.d# vi mysqld.cnf
没错,又是这个配置文件#修改bind-address127.0.0.1为0.0.0.0 bind-address = 0.0.0.0
修改之后别忘记保存:wq哦,第一步完成,接下来是第二步:root@VM-202-164-ubuntu:~# mysql -u root -p Enter password: mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "ROOT"; --ROOT为密码 mysql> flush privileges;
授权root用户的远程访问。接下来是重启MySQL:root@VM-202-164-ubuntu:~# /etc/init.d/mysql restart
最后,验证试试,看看还显不显示连接失败。 - 再给几个常用MySQL命令:
#MySQL服务器开启,关闭,重启 /etc/init.d/mysql start /etc/init.d/mysql stop /etc/init.d/mysql restart
- 在调试java程序的时候抛出一个异常(具体是什么不记得了),反正有max_allowed_packet这个字段的提示,很明显,由于insert或者update语句中包含长文本,太长了超过了某个阈值(其实就是你一次insert的东西太大了,MySQL为保证IO的正常而设置的阈值),而这个阈值是在MySQL服务端设置的,不管你有没有遇到这个异常呢,先记下来吧,以备不时之需。其实设置步骤很简单:
root@VM-202-164-ubuntu:~# mysql -u root -p Enter password: mysql> show VARIABLES like '%max_allowed_packet%'; mysql> set global max_allowed_packet = 2*1024*1024*10;
其实就是设置max_allowed_packet这个值啦,单位是b,所以2*1024*1024*10代表20M。然后是重启,这个不重启是不生效的。root@VM-202-164-ubuntu:~# /etc/init.d/mysql restart
最后验证一下:root@VM-202-164-ubuntu:~# mysql -u root -p Enter password: mysql> show VARIABLES like '%max_allowed_packet%';
![酷 酷](https://i-blog.csdnimg.cn/blog_migrate/50bb552c53801f5e0190347ee7c11d0c.gif)