Linux(Ubuntu,Debian,Centos)安装配置mysql并且远程连接数据库

1.Ubuntu 16

#1.安装
sudo apt-get install mysql-server mysql-client libmysqlclient-dev
#2.检查是否安装成功
sudo netstat -tap | grep mysql
#3.启动服务
service mysql start
service mysql stop
service mysql status

#4.登陆,不用输密码直接enter
mysql -u root -p
#重置root密码
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

#创建新用户
create user 'admin' identified by '123456';

#5.数据库操作
#（1）创建数据库
create database testdb；
#（2）显示所有数据库
show databases;
#（3）切换数据库
use testdb;
#（4）创建表
create table test(
id int not null primary key auto_increment,
name varchar(20));
#（5）查询表中所有数据
select * from test;

#6.远程连接设置
mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
flush privileges;

如果navicat连接不上mysql：

#查看3306端口，说明3306只绑定到本地主机上
netstat -an|grep 3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN
#修改配置文件，注释掉bind-address = 127.0.0.1
vim /etc/mysql/mysql.conf.d/mysqld.cnf
#重启服务
service mysql restart
#再次查看3306端口，现在3306是绑定到所有远程主机了
netstat -an|grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN

2.Debian

apt-get install mysql-server mysql-client

与Ubuntu类似，不要sudo

授权远程连接后用navicat连接会报错：
2003 - Can’t connect to MySQL server on ‘ip’ (60 “Operation timed out”)

解决办法：

vim /etc/mysql/mariadb.conf.d/50-server.cnf

#bind-address	= 127.0.0.1

3.Centos

#1.下载并安装MySQL官方的Yum Repository
wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
yum -y install mysql57-community-release-el7-10.noarch.rpm
#2.安装MySQL服务器
yum -y install mysql-community-server
#3.启动MySQL
systemctl start  mysqld.service
#查看MySQL运行状态
systemctl status mysqld.service
#4.此时MySQL已经开始正常运行，需要找出root的密码
grep "password" /var/log/mysqld.log
#5.登录mysql
mysql -uroot -p
#6.输入初始密码，此时不能做任何事情，因为MYSQL默认必须修改密码才能正常使用
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password';
# 这里会遇到一个问题，新密码设置过于简单会报错，可通过如下命令修改
mysql> set global validate_password_policy=0;
mysql> set global validate_password_length=1;
#7.还有一个问题就是Yum Repository,以后每次 yum 操作都会自动更新，需要把这个卸载掉
yum -y remove mysql57-community-release-el7-10.noarch
#8.执行和ubuntu相同的远程连接授权命令,添加用户命令；

授权远程连接后用navicat连接会报错：
2003 - Can’t connect to MySQL server on ‘ip’ (60 “Operation timed out”)

解决办法：从 授权，端口，防火墙三个方面考虑问题:

1.授权，已经授权了啊:

mysql> show grants for admin;
+--------------------------------------------------------------+
| Grants for admin@%                                           |
+--------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

2.查看端口：对外任意IP，任意端口，没问题：

netstat -tunlp

Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp6       0      0 :::3306                 :::*                    LISTEN      1814/mysqld 

3.防火墙开放3306端口：解决问题

firewall-cmd --zone=public --add-port=3306/tcp --permanent

systemctl restart firewalld.service