centos6.9下安装mysql5.5.35

1、将安装包MySQL-server-5.5.35-1.linux2.6.x86_64通过ssh工具上传到虚拟机中。

2、使用命令rpm -ivh MySQL-server-5.5.35-1.linux2.6.x86_64.rpm安装

过程中出现问题1：

 file /usr/share/mysql/binary-configure from install of MySQL-server-5.5.35-1.linux2.6.x86_64 conflicts with file from package MySQL-server-5.5.59-1.el7.x86_64

解决方法1：

 yum -y remove MySQL-server-5.5.59-1.el7.x86_64

重新执行 rpm -ivh MySQL-server-5.5.35-1.linux2.6.x86_64.rpm

过程中出现问题2：

Starting MySQL.................................................................................................
D file (/var/lib/mysql/localhost.localdomain.pid).         [失败]

解决方法2：

使用命令vi /var/lib/mysql/localhost.localdomain.err，发现如下错误

InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process

可能是ibddata已经有人读取了，无法取得其权限，不能再次启动，通过命令ps aux|grep mysq* 查看当前运行mysql进程

root      29395  0.0  4.3 422424 43260 ?        Sl   Sep27   0:15 mysqld --user=root
root      53027  0.0  0.0 103344   860 pts/2    S+   09:47   0:00 grep mysql

使用命令kill 29395，杀掉进程

3、使用命令service mysql restart 重新启动mysql，启动成功

4、使用命令show variables like 'character%'，查看并修改数据库编码方式

 

mysql> show  variables like 'character%';
+--------------------------+----------------------------+
| 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/share/mysql/charsets/ |
+--------------------------+----------------------------+

需要在my.cnf中修改mysql数据库编码方式

过程中出现问题3：

/etc/下没有my.cnf

解决方法3：

cp /usr/share/mysql/my-medium.cnf  /etc/my.cnf

vi /etc/my.cnf

在[client]下添加default_character_set=utf8，

在[mysqld]下添加collation_server = utf8_general_ci
character_set_server = utf8

重新启动mysql，service mysql restart

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+

5、修改mysql数据库root用户密码

mysql> use mysql;
Database changed
mysql> update user set password=password('123456') where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> exit

6、对远程访问授权

 grant all privileges on  *.*  to root@'%'  identifies  by 'xxxxxx';

flush privileges;

其中xxxxx表示root用户的密码

7、导入sql文件

mysql> create database platformhlj;

mysql> source /usr/local/platformhlj.sql;

过程中出现问题4：

[Err] 1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

解决方法4：

 vi /etc/my.cnf

在my.cnf中添加log_bin_trust_function_creators=1

 重启mysql，service mysql restart

mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+
1 row in set (0.00 sec)

mysql> source /usr/local/platformhlj.sql;

导入成功；

8、将mysql设置为开机自启动（如何在定时器中设置定时检测，这一步可以省略）

将服务文件拷贝到init.d下，命名为mysqld

[root@localhost etc]# cp /usr/share/mysql/mysql.server  /etc/init.d/mysqld

赋予可执行权限

[root@localhost etc]# chmod +x /etc/init.d/mysqld

添加服务

[root@localhost etc]# chkconfig --add mysqld

显示服务列表

[root@localhost etc]# chkconfig --list

mysqld          0:关闭  1:关闭  2:启用  3:启用  4:启用  5:启用  6:关闭

重启电脑，查看mysql服务

[root@localhost etc]# reboot

[root@localhost ~]# netstat -apn|grep 3306
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      2182/mysqld

9、在定时器中设置定时检测mysql

在定时器中添加定时执行的脚本crontab -e

*/5 * * * * /usr/local/crondmysql.sh

crondmysql.sh脚本的内容如下所示（网上搜索内容，不是自己写的）：

#!/bin/bash
pgrep -x mysqld &> /dev/null
if [ $? -ne 0 ]
then
echo "At time: `date` :MySQL is stop .">> /usr/local/mysqlstatus.txt
/etc/init.d/mysqld start
else
echo “MySQL server is running .”
fi

10、定时备份mysql

创建fullbackmysql.sh脚本，脚本中的内容如下：

mysqldump -u root -p123456 platformhlj >/usr/local/backmysql/$(date +%Y%m%d)platform.sql

在定时器中定时(每天23:30）执行该脚本

30 23 * * * /usr/local/fullbackmysql.sh

11、创建删除备份文件的脚本/deleteback.sh，如果备份的文件大于3个，删除时间最小的备份文件，使备份的文件夹中始终保持四份时间最新的全量备份。脚本内容如下所示：

count=`ls /usr/local/backmysql | wc -w`
if [ $count>3 ]
 then
   date2=$(date +%Y%m%d)
   t=`date -d "$date2" +%s`
   for file in /usr/local/backmysql/*
     do
         date1=${file:21:8}
         t1=`date -d "$date1" +%s`
       #  echo $t1
         if [ $t1 -lt $t ];then
           t=$t1
           date2=$date1
         fi
     done
     rm /usr/local/backmysql/${date2}platform.sql
 fi

在定时在中定时（每天23：05）执行该脚本

05 23 * * * /usr/local/fullbackmysql.sh

12、还原备份文件

source /usr/local/backmysql/20181027pl.sql