ubuntu18 配置Mysql

Ubuntu 18.04的mysql配置跟16.04有些略微不同。。。踩了不少坑

先清空旧版本的mysql

sudo apt-get remove --purge mysql-\*
sudo apt-get install mysql-server mysql-client

但出现报错信息如下:

mysqld: Can't read dir of '/etc/mysql/mysql.conf.d/' (Errcode: 13 - Permission denied)

解决方法:重装apparmor或者按照网上所说的方法:Unable to install mysql with Can’t read dir of ‘/etc/mysql/conf.d/‘ error

sudo /etc/init.d/apparmor stop
# sudo systemctl stop apparmor.service
sudo /etc/init.d/apparmor teardown

安装mysql

sudo apt install mysql-server
sudo apt install mysql-client
sudo apt install libmysqlclient-dev

安装完后可以用下述命令看下mysql是否正常启动

sudo netstat -tap | grep mysql

从Ubuntu 18开始mysql不是默认安装了,并且在安装时也不会提示输入密码等操作,所以正常的mysql -uroot -p是无法登陆了,这时得从安装后的配置文件/etc/mysql/debian.cnf中获取

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = NfF39kxWuGmjJozf
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = NfF39kxWuGmjJozf
socket   = /var/run/mysqld/mysqld.sock

从上述配置文件中可看出,apt安装mysql时设定的默认用户名是debian-sys-maint,而密码则是一个随机的字符NfF39kxWuGmjJozf

所以先用上述用户名和密码先登陆到mysql中再修改账户和密码

mysql -udebian-sys-maint -p
mysql> use mysql;
mysql> update mysql.user set authentication_string=PASSWORD('newpassword'), plugin='mysql_native_password' where user='root';
mysql> flush privileges;

退出后,就能mysql -uroot -p登陆了

ERROR 1067 (42000): Invalid default value for 'post_date'

这是因为sql_mode中的NO_ZEROR_DATE导制的,在strict mode中不允许’0000-00-00’作为合法日期

使用show variables like 'sql_mode';命令查看sql_mode

+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

通过set sql_mode修改是临时修改,因此需要加个global参数

mysql> set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> commit;
mysql> exit

然后再重新导入数据库wp.sql

mysql> create database wp;
mysql> use wp;
mysql> source /home/anlan/test.sql;

本文出自于http://www.bioinfo-scrounger.com转载请注明出处

文章目录
|