Linux搭建mysql环境

1.MySQL的安装

下载链接：https://download.csdn.net/download/qq_43625140/13799700

# 显示安装包
[root@zhouwei home]# ls
mysql-5.6.48-linux-glibc2.12-x86_64.tar.gz

# 解压安装包
[root@zhouwei home]# tar -zxvf mysql-5.6.48-linux-glibc2.12-x86_64.tar.gz

# 删除安装包
[root@zhouwei home]# rm mysql-5.6.48-linux-glibc2.12-x86_64.tar.gz

# 移动并重命名
[root@zhouwei home]# mv /home/mysql-5.6.48-linux-glibc2.12-x86_64/ /usr/local/mysql

# 创建特殊的账号，叫做mysql（所属组mysql）
[root@zhouwei home]# useradd -r -s /sbin/nologin mysql

# 更改/usr/local/mysql目录权限，更改文件拥有者与所属组都必须是mysql
[root@zhouwei home]# chown -R mysql.mysql /usr/local/mysql/

# yum安装autoconf
[root@zhouwei home]# yum -y install autoconf

# yum安装mariadb库文件
[root@zhouwei home]#yum install libaio* -y

# 初始化数据库
[root@zhouwei home]# /usr/local/mysql/scripts/mysql_install_db --user=mysql

# 验证初始化数据库是否成功（出现mysql系统数据库则成功）
[root@zhouwei home]# ll /usr/local/mysql/data

# 复制并移动mysql服务
[root@zhouwei home]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

# 启动mysql服务
[root@zhouwei home]# service mysql start

# 设置root用户密码（牢记root密码）
[root@zhouwei home]# /usr/local/mysql/bin/mysqladmin -u root password '密码'

# 使用root用户连接mysql服务
[root@zhouwei home]# /usr/local/mysql/bin/mysql -u root -p (回车输入密码)

# 输入SQL语句查看数据库（出现mysql提供的系统数据库）
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.10 sec)

# 停止mysql服务
[root@zhouwei home]# service mysql stop

2. 配置环境变量

# 用vim编辑器打开/etc/profile文件
[root@zhouwei ~]# vim /etc/profile

# 在文件末尾添加mysql环境变量
export MYSQL_HOME=/usr/local/mysql
export PATH=$MYSQL_HOME/bin:$PATH

# 重启机器后生效，开启mysql服务，在任意位置可以连接mysql服务
[root@zhouwei ~]# mysql -h 127.0.0.1 -P 3306 -u root -p

3. 修改字符集

# 连接mysql服务
[root@zhouwei ~]# mysql -h 127.0.0.1 -P 3306 -u root -p

# 查看数据库字符集
mysql> show variables like 'chara%';

# 发现character_set_server使用的是latin1
mysql> show variables like 'chara%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | latin1                           |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | latin1                           |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

# 退出mysql服务
mysql> exit;

# 关闭mysql服务
[root@zhouwei ~]# service mysql stop

# 使用vim编辑器打开mysql安装目录下的my.cnf文件
[root@zhouwei ~]# vim /usr/local/mysql/my.cnf

# 添加字符集设置（没有[client]自己手写一个）
[client]
default-character-set=utf8

[mysqld]
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci

4. 开启远程连接

# 连接mysql服务
[root@zhouwei ~]# mysql -h 127.0.0.1 -P 3306 -u root -p

# 切换数据库
mysql> USE `mysql`;

# 查看use表（发现root用户只能在本机登录）
mysql> SELECT `host`,`user` FROM `user`;
+-----------+------+
| host      | user |
+-----------+------+
| 127.0.0.1 | root |
| ::1       | root |
| localhost |      |
| localhost | root |
| zhouwei   |      |
| zhouwei   | root |
+-----------+------+
6 rows in set (0.00 sec)

# 添加root远程连接
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '密码';

# 刷新权限
mysql> FLUSH PRIVILEGES;

# 查看user表（host存在%记录）
mysql> SELECT `host`,`user` FROM `user`;
+-----------+------+
| host      | user |
+-----------+------+
| %         | root |
| 127.0.0.1 | root |
| ::1       | root |
| localhost |      |
| localhost | root |
| zhouwei   |      |
| zhouwei   | root |
+-----------+------+
7 rows in set (0.00 sec)

# 设置Linux防火墙，开放3306端口
[root@zhouwei ~]# /sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT

5. 开机自动启动

# 将mysql服务加到系统服务
[root@zhouwei ~]# chkconfig --add mysql

# 开启开机自动启动
[root@zhouwei ~]# chkconfig mysql on

# 关闭开机自动启动
[root@zhouwei ~]# chkconfig mysql off