SQL服务器搭建

Ubuntu 16.04.2 LTS

sudo apt-get update
sudo apt-get install apache2
sudo systemctl restart apache2

sudo apt-get install mysql-server
mysql_secure_installation
sudo apt-get install php libapache2-mod-php php-mcrypt php-mysql
sudo apt install unzip
#sudo apt-get install php-mbstring

cd /var/www/html/
wget https://files.phpmyadmin.net/phpMyAdmin/4.7.6/phpMyAdmin-4.7.6-all-languages.zip
unzip -x *.zip
mv phpMyAdmin-4.7.6-all-languages phpmyadmin
sudo systemctl restart apache2

/sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT

1.ping 远程地址 判断远端服务网络正常

2.telnet 远程地址 端口号,拒绝了访问3306端口
telnet 192.168.99.100 3306

3.
vi /etc/mysql/mysql.conf.d/mysqld.cnf
修改MySQL配置文件,将bind_address = 127.0.0.1注释掉或修改成0.0.0.0
sudo /etc/init.d/mysql restart

netstat -an|grep 3306
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN

4.编辑后重启mysql ,再次查看3306端口
telnet 192.168.99.100 3306

5.授权访问,
用root身份登录到mysql,使用grant命令分配权限,
如果操作所有库就把 database_name.* 改成 *.* ,user_name是用户名,%是所有ip地址可访问,
如果限制固定ip访问就改成ip,user_password是密码,
mysql -uroot -p
mysql> grant all on database_name.* to user_name@'%' identified by 'user_password';
mysql> flush privileges; #让权限立即生效
mysql> exit;

开启慢查询
mysql -uroot -p
set global slow_query_log=ON;
set global long_query_time=1;
set global log_queries_not_using_indexes = 1;
将字符编码设置为UTF-8
set character_set_database=utf8;
set character_set_server=utf8;

show variables like '%char%';

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.00 sec)

phpmyadmin
Users overview
User name Host Password Global privileges Grant
root % Yes ALL PRIVILEGES Yes

客户端连接:

mysql -u 用户名 -p密码 -h 服务器IP地址 -P 服务器端MySQL端口号 -D 数据库名

注意:
(1)服务器端口标志-P一定要大些以区别于用户-p,如果直接连接数据库标志-D也要大写;
(2)如果要直接输入密码-p后面不能留有空格如-pmypassword;
(3)命令结束段没有';'分号。

例子:
mysql -u lzpddd -pmypassword -h 192.168.1.88 -P 3306 -D mydb

发表评论

电子邮件地址不会被公开。 必填项已用*标注