1.安装数据库
sudo apt-get install mysql-server sudo apt-get install mysql-client sudo apt-get install libmysqlclient-dev
查看服务运行状态
sudo systemctl status mysql.service
还原数据库
mysql> -u root -p dbname
修改密码
mysql> set password for root@localhost = password('pwd');
开启远程连接
mysql> use mysql;
mysql> update user set authentication_string=password("pwd"),plugin='mysql_native_password' where user='root';
mysql> grant all privileges on *.* to 'root'@'%' identified by 'pwd';
mysql> quit;
服务重启
service mysql restart
刷新权限
mysql> flush privileges;
Ubuntu mysql连接错误10060/10061的方法
mysql 10060错误一般因为防火墙
ufw allow 3306; //允许外部访问3306端口
ufw allow from 192.168.1.115; //允许此IP访问所有的本机端口
ufw status; //查看防火墙状态
ufw disable/enable; //关闭或打开防火墙
没装ufw就执行安装既可：apt-get install ufw;
mysql 10061错误为配置原因
第一步：
先查看mysql运行状态 netstat -ntlp | grep -v tcp6; //查看端口状态 mysql -V; //查看mysql版本号 find / -name mysqld.cnf; // 查找mysqld.cnf 的位置
如果端口为127.0.0.1:3306，解决办法 mysql5.7 版本： vi /etc/mysql/mysql.conf.d/mysqld.cnf; //将bind-address = 127.0.0.1 修改成 bind-address = 0.0.0.0
mysql5.7以前的版本： vi /etc/mysql/my.cnf; //将bind-address = 127.0.0.1 修改成 bind-address = 0.0.0.0 ，或注释掉即可
service mysql restart; //重启mysql netstat -ntlp | grep -v tcp6; //查看端口状态为0.0.0.0:3306即可
第二步：
mysql -uroot -p123;//登录mysql use mysql;//进入mysql数据库 select Host,User from user;//查看user表中root是否支持外部访问，localhost 只支持本地； IP地址只支持 Host User localhost root //只支持本地 % root //全部IP都允许 192.168.1.115 root //只支持此IP
GRANT ALL PRIVILEGES ON . TO 'root'@'192.168.1.115' IDENTIFIED BY '123' WITH GRANT OPTION; //给此IP地址授权 FLUSH PRIVILEGES;//刷新该表即可
调优参数：
SHOW VARIABLES LIKE 'max_connections';
SET GLOBAL wait_timeout = 300; SET GLOBAL interactive_timeout = 300;
SHOW PROCESSLIST;
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads%';
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'max_allowed_packet';
SHOW VARIABLES LIKE 'wait_timeout'; SHOW VARIABLES LIKE 'interactive_timeout';
SET GLOBAL wait_timeout = 300; SET GLOBAL interactive_timeout = 300; SET GLOBAL max_connections = 1000;
67108864 67108864 select 64 * 1024 * 1024;
SHOW VARIABLES LIKE 'max_connections'; -- 1000
SHOW VARIABLES LIKE 'max_allowed_packet'; -- 1073741824 SHOW VARIABLES LIKE 'slave_max_allowed_packet'; -- 1073741824
SHOW VARIABLES LIKE 'wait_timeout'; -- 300 SHOW VARIABLES LIKE 'interactive_timeout'; -- 300