ubuntu16.04 运用mysql——Aliyun轻量级应用服务器上

1、安装与卸载

1.1 安装

ubuntu上安装mysql非常简单只需要几条命令就可以完成。

sudo apt-get install mysql-server          //服务端
sudo apt-get install mysql-client          //客户端
sudo apt-get install libmysqlclient-dev    //程序编译时链接的库

mysql默认端口是3306

1.2 卸载

# step1:删除MySQL服务器
sudo apt-get remove mysql-server
# step2:删除随MySQL服务器自动安装的任何其他软件
sudo apt-get autoremove
# step3:卸载其他组件
sudo apt-get remove （package-name）
# 查看从MySQL APT存储库安装的软件包列表
dpkg -l |grep mysql |grep ii

1.3 相关文件管理

通过这种方式安装好之后开机自启动都已经配置好，和命令行上的环境变量，无需手动配置。
安装好后会创建如下目录：
数据库目录： /var/lib/mysql
配置文件：/usr/share/mysql（命令行配置文件），/etc/mysql（如，my.conf）
相关命令： /usr/bin(mysqladmin,mysqldump等命令)和 /usr/sbin
启动脚本：/etc/init.d/mysql（启动脚本文件mysql的目录）

2、数据库的访问

2.1 命令行访问

# 启动
sudo service mysql start
# 停止
sudo service mysql stop
#服务器状态
sudo service mysql status

# 本地登录数据库
mysql -u root -p 
# -u 表示选择登陆的用户名， -p 表示登陆的用户密码，上面命令输入之后会提示输入密码，此时输入密码就可以登录到mysql。

# 远程连接数据据
mysql -h 127.0.0.1 -P 3306 -u root -p 123456
# -h 为远程登录，-P为端口号，-u为用户名，-p为密码

# 服务器启动后端口查询
sudo netstat -anp |grep mysql

>> show databases;
>> use mysql;
>> show tables;

4.2 图形界面访问

Navicat——免费试用14天

HeidiSQL——开源

4.3 python/C++ 访问

python2和python3访问linux的安装包不同
python3：
pip install PyMySQL
import pymysql

写一个简单的程序来访问该数据库，实现show tables 的功能；

import pymysql

conn = pymysql.connect("IP_addr", "root", "mysql_password", "database_name", charset = "utf8")

cursor = conn.cursor()

sql =  """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""

cursor.execute(sql)
cursor.close()
conn.close()

4.4 Django访问

pip 安装 mysqlclient 失败
解决方法：
apt-get install libmysqlclient-dev python3-dev

(python36) root@ljpdev:/home/wk-api# apt-get install libmysqlclient-dev python3-dev
Reading package lists... Done
Building dependency tree
Reading state information... Done
python3-dev is already the newest version.
The following packages were automatically installed and are no longer required:
  libdbi-perl libterm-readkey-perl
Use 'apt-get autoremove' to remove them.
The following extra packages will be installed:
  libmysqlclient18 mysql-common
The following NEW packages will be installed:
  libmysqlclient-dev libmysqlclient18 mysql-common
0 upgraded, 3 newly installed, 0 to remove and 75 not upgraded.
Need to get 0 B/1,694 kB of archives.
After this operation, 9,390 kB of additional disk space will be used.
Do you want to continue? [Y/n] y
Selecting previously unselected package mysql-common.
(Reading database ... 80571 files and directories currently installed.)
Preparing to unpack .../mysql-common_5.5.62-0+deb8u1_all.deb ...
Unpacking mysql-common (5.5.62-0+deb8u1) ...
Selecting previously unselected package libmysqlclient18:amd64.
Preparing to unpack .../libmysqlclient18_5.5.62-0+deb8u1_amd64.deb ...
Unpacking libmysqlclient18:amd64 (5.5.62-0+deb8u1) ...
Selecting previously unselected package libmysqlclient-dev.
Preparing to unpack .../libmysqlclient-dev_5.5.62-0+deb8u1_amd64.deb ...
Unpacking libmysqlclient-dev (5.5.62-0+deb8u1) ...
Processing triggers for man-db (2.7.0.2-5) ...
Setting up mysql-common (5.5.62-0+deb8u1) ...
Setting up libmysqlclient18:amd64 (5.5.62-0+deb8u1) ...
Setting up libmysqlclient-dev (5.5.62-0+deb8u1) ...
Processing triggers for libc-bin (2.19-18+deb8u10) ...

安装好后，就好使了
pip install mysqlclient

(python36) root@ljpdev:/home/wk-api# pip install mysqlclient
Collecting mysqlclient
  Downloading https://mirrors.aliyun.com/pypi/packages/f4/f1/3bb6f64ca7a429729413e6556b7ba5976df06019a5245a43d36032f1061e/mysqlclient-1.4.2.post1.tar.gz (85kB)
    100% |████████████████████████████████| 92kB 47.8MB/s
Installing collected packages: mysqlclient
  Running setup.py install for mysqlclient ... done
Successfully installed mysqlclient-1.4.2.post1
You are using pip version 9.0.1, however version 19.2.1 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.

5、外网无法访问Aliyun服务器上的mysql数据库

考虑四个因素：

• 1、Aliyun自带防火墙，其3306端口是否对外开放
  
• 2、Aliyun服务器的防火墙是否开放
  
• 3、mysql中的user是否授权其他主机连接
  
• 4、mysql服务器的配置是否更改