centos6.5环境通达OA数据库mysql5.0.67升级至mysql5.5.48方案
centos6.5环境通达OA数据库mysql5.0.67升级至mysql5.5.42方案
1、停用生产环境的应用访问
直接修改web的访问端口，避免在更换过程中有人访问
Listen 1888
2、停用数据库服务并备份数据库
# /opt/lampp/lampp stopmysql
# cd /opt/lampp/var/mysql
# cp -r TD_OA TD_OA20160409
将备份文件上传到目标服务器8.68
3、添加系统自动添加账号的函数
4、修改原8.200的IP为8.201，备机服务器8.68IP更换为8.200
修改前端数据库连接
/opt/lampp/htdocs/MYOA/webroot/inc/oa_config.php
5、启动mysql服务，测试能否正常连接
# service mysqld start
6、测试常用的流程，把之前修改的前端listen端口该回来成8888，恢复访问
7、启用数据库同步
查看是否已经安装了gcc
# rpm -qa | grep gcc
# yum install -y gcc-c++
# yum install -y cmake
# yum install -y git
解决依赖关系
# yum install readline-devel zlib-devel openssl-devel
Warning: Bison executable not found in PATH
解决方法：
# yum install -y bison
再次编译即通过
# cd mysql-5.5.48
# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mydata -DSYSCONFDIR=/etc -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_LIBWRAP=0 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
# make && make install
编译报错需要删除编译缓存
rm CMakeCache.txt
# fdisk /dev/sda
n
p
3
+20G
w
#挂载系统分区
# kpartx -l /dev/sda
# kpartx -af /dev/sda
# partx -a /dev/sda
验证是否挂载成功
# cat /proc/partitions
创建物理卷
# pvcreate /dev/sda3
创建名为myvg的卷组
# vgcreate myvg /dev/sda3
#创建一个大小为15G，名字叫做mylv的逻辑卷
# lvcreate -L 15G -n mylv myvg
格式化
# mke2fs -t ext4 -b 2048 /dev/myvg/mylv
# mkdir -pv /data/mydata
mkdir: created directory ‘/data’
mkdir: created directory ‘/data/mydata’
# mount /dev/myvg/mylv /data/mydata
# vim /etc/fstab
添加如下内容：
/dev/myvg/mylv /data/mydataext4defaults,noatime 0 0
# groupadd -r -g 306 mysql
# useradd -g mysql -r -g 306 -s /sbin/nologin mysql
# id mysql
uid=994(mysql) gid=306(mysql) groups=306(mysql)
改变数据存储目录和安装目录的权限
# cd /usr/local/mysql
添加存放日志的目录
# mkdir /data/binlogs
# chown -R :mysql ./*
# chown -R mysql.mysql /data/mydata
# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
# cp support-files/my-large.cnf /etc/my.cnf
编辑/etc/my.cnf配置，加入以下内容
innodb_file_per_table = ON
datadir = /data/mydata
log-bin=/data/binlogs/mysql-bin
把mysqld服务加入到启动项
# chkconfig --add mysqld
# chkconfig --list mysqld
将mysql命令加入环境变量中
vim /etc/profile.d/mysql.sh
加入
export PATH=/usr/local/mysql/bin:$PATH
启动mysqld服务
# service mysqld start
报错
Starting MySQL. ERROR! The server quit without updating PID file (/data/mydata/oadb-test.pid).
# ss -tnl
可以观察日志/data/mydata/oadb-test.err，是日志创建权限的问题
# chown -R mysql.mysql /data
# chown -R mysql.mysql /data/mydata
# service mysqd restart
对mysql数据库用户进行清理，加密码
mysql> use mysql
Database changed
mysql> select user,host,password from user;
+------+-----------+----------+
| user | host | password |
+------+-----------+----------+
| root | localhost | |
| root | adb-test | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | adb-test | |
+------+-----------+----------+
6 rows in set (0.01 sec)
mysql> delete from user where user='' and host='localhost';
Query OK, 1 row affected (0.01 sec)
mysql> delete from user where user='' and host='adb-test';
Query OK, 1 row affected (0.00 sec)
设置其中root密码为myoa888，并对服务器IP限制
mysql> update user set password=PASSWORD('myoa888'),host='192.168.11.144' where user='root' and host='::1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
对root用户加密
mysql> update user set password=PASSWORD('13C1325E831DEC8D60') where user='root' and host in('localhost','adb-test','127.0.0.1');
mysql> select user,host,password from user;
+------+----------------+-------------------------------------------+
| user | host | password |
+------+----------------+-------------------------------------------+
| root | localhost | *CF51F9E245F41378E51B4154082F26108A5B0D63 |
| root | adb-test | *CF51F9E245F41378E51B4154082F26108A5B0D63 |
| root | 127.0.0.1 | *CF51F9E245F41378E51B4154082F26108A5B0D63 |
| root | 192.168.11.144 | *91AF99F23C3D4ED85140D100433725DFA52BECEE |
TD_OA
字符集gbk -- GBK Simplified Chinese
排序规则gbk_chinese_ci
②建立编码表
CREATE TABLE `cs_char2letter` (
`PY` char(1) character set utf8 NOT NULL,
`HZ` char(1) NOT NULL default '',
PRIMARY KEY (`PY`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
③插入
truncate table cs_char2letter;
set names gbk;
insert into cs_char2letter values
('A','骜'),
('B','簿'),
('C','错'),
('D','鵽'),
('E','樲'),
('F','鳆'),
('G','腂'),
('H','夻'),
('J','攈'),
('K','穒'),
('L','鱳'),
('M','旀'),
('N','桛'),
('O','沤'),
('P','曝'),
('Q','囕'),
('R','鶸'),
('S','蜶'),
('T','箨'),
('W','鹜'),
('X','鑂'),
('Y','韵'),
('Z','咗');
④建立函数
DELIMITER $$
DROP FUNCTION IF EXISTS `test`.`func_get_first_letter`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `func_get_first_letter`(words varchar(255)) RETURNS char(1) CHARSET gbk
BEGIN
declare fpy char(1);
declare pc char(1);
declare cc char(4);
set @fpy = UPPER(left(words,1));
set @pc = (CONVERT(@fpy USING gbk));
set @cc = hex(@pc);
if @cc >= "8140" and @cc <="FEA0" then
begin
select PY from cs_char2letter where hz>=@pc limit 1 into @fpy;
end;
end if;
Return @fpy;
END$$
DELIMITER;
报错：
This function has none of DETERMINISTIC, NO SQL解决办法
创建存储过程时
出错信息：
[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)
原因：
这是我们开启了bin-log, 我们就必须指定我们的函数是否是
1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句，当然也不会修改数据
3 READS SQL DATA 只是读取数据，当然也不会修改数据
4 MODIFIES SQL DATA 要修改数据
5 CONTAINS SQL 包含了SQL语句
其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
解决方法：
SQL code
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
1 row in set (0.00 sec)
mysql> set global log_bin_trust_function_creators=1;
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON |
+---------------------------------+-------+
这样添加了参数以后，如果mysqld重启，那个参数又会消失，因此记得在/etc/my.cnf配置文件中添加：
log_bin_trust_function_creators=1
mkdir -pv /backup/mysqlbak
修改属主属组
chown -R mysql.mysql /backup
编辑shell脚本
vim /usr/sbin/bakmysql
加入如下内容：
#关闭mysql数据库服务
service mysqld stop
#创建以当前日期为名称的目录
cd /backup/mysqlbak
time=$(date '+%Y%m%d')
mkdir $time
cp /data/mydata/TD_OA/*.* /backup/mysqlbak/$time
#备份完成后开启mysql数据库服务
service mysqld start
#删除15天以前的备份
find /backup/mysqlbak -type d -mtime +15 -exec rm -rf {} \;
3、修改文件属性，使其可执行
chmod +x /usr/sbin/bakmysql
4、修改/etc/crontab
vim /etc/crontab
#每天3点执行脚本
01 3 * * * mysql /usr/sbin/bakmysql
5、重新启动crond
/etc/rc.d/init.d/crond restart
记得添加
#@student - maxlogins 4
* - nproc 1006154
* - nofile 1006154
# End of file
整体方案：
环境准备，在备用服务器安装mysql5.5数据库1、停用生产环境的应用访问
直接修改web的访问端口，避免在更换过程中有人访问
Listen 1888
2、停用数据库服务并备份数据库
# /opt/lampp/lampp stopmysql
# cd /opt/lampp/var/mysql
# cp -r TD_OA TD_OA20160409
将备份文件上传到目标服务器8.68
3、添加系统自动添加账号的函数
4、修改原8.200的IP为8.201，备机服务器8.68IP更换为8.200
修改前端数据库连接
/opt/lampp/htdocs/MYOA/webroot/inc/oa_config.php
5、启动mysql服务，测试能否正常连接
# service mysqld start
6、测试常用的流程，把之前修改的前端listen端口该回来成8888，恢复访问
7、启用数据库同步
具体实施步骤
一、安装cmake编译工具和依赖环境
跨平台编译器查看是否已经安装了gcc
# rpm -qa | grep gcc
# yum install -y gcc-c++
# yum install -y cmake
# yum install -y git
解决依赖关系
# yum install readline-devel zlib-devel openssl-devel
Warning: Bison executable not found in PATH
解决方法：
# yum install -y bison
再次编译即通过
二、编译安装mysql-5.5.48
# tar xf mysql-5.5.48.tar.gz# cd mysql-5.5.48
# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mydata -DSYSCONFDIR=/etc -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_LIBWRAP=0 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
# make && make install
编译报错需要删除编译缓存
rm CMakeCache.txt
三、新建存放Mysql数据库文件的逻辑卷并挂载到/data/mydata下
分区# fdisk /dev/sda
n
p
3
+20G
w
#挂载系统分区
# kpartx -l /dev/sda
# kpartx -af /dev/sda
# partx -a /dev/sda
验证是否挂载成功
# cat /proc/partitions
创建物理卷
# pvcreate /dev/sda3
创建名为myvg的卷组
# vgcreate myvg /dev/sda3
#创建一个大小为15G，名字叫做mylv的逻辑卷
# lvcreate -L 15G -n mylv myvg
格式化
# mke2fs -t ext4 -b 2048 /dev/myvg/mylv
# mkdir -pv /data/mydata
mkdir: created directory ‘/data’
mkdir: created directory ‘/data/mydata’
# mount /dev/myvg/mylv /data/mydata
# vim /etc/fstab
添加如下内容：
/dev/myvg/mylv /data/mydataext4defaults,noatime 0 0
四、创建Mysql用户
添加mysql用户指定组id和用户id为306# groupadd -r -g 306 mysql
# useradd -g mysql -r -g 306 -s /sbin/nologin mysql
# id mysql
uid=994(mysql) gid=306(mysql) groups=306(mysql)
改变数据存储目录和安装目录的权限
# cd /usr/local/mysql
添加存放日志的目录
# mkdir /data/binlogs
# chown -R :mysql ./*
# chown -R mysql.mysql /data/mydata
五、启动脚本初始化数据库
# scripts/mysql_install_db --user=mysql --datadir=/data/mydata^C# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
# cp support-files/my-large.cnf /etc/my.cnf
编辑/etc/my.cnf配置，加入以下内容
innodb_file_per_table = ON
datadir = /data/mydata
log-bin=/data/binlogs/mysql-bin
把mysqld服务加入到启动项
# chkconfig --add mysqld
# chkconfig --list mysqld
将mysql命令加入环境变量中
vim /etc/profile.d/mysql.sh
加入
export PATH=/usr/local/mysql/bin:$PATH
启动mysqld服务
# service mysqld start
报错
Starting MySQL. ERROR! The server quit without updating PID file (/data/mydata/oadb-test.pid).
# ss -tnl
可以观察日志/data/mydata/oadb-test.err，是日志创建权限的问题
# chown -R mysql.mysql /data
六、上传通达OA数据库并配置相关权限
上传通达OA数据库到服务器上，修改权限，并重启服务，测试# chown -R mysql.mysql /data/mydata
# service mysqd restart
对mysql数据库用户进行清理，加密码
mysql> use mysql
Database changed
mysql> select user,host,password from user;
+------+-----------+----------+
| user | host | password |
+------+-----------+----------+
| root | localhost | |
| root | adb-test | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | adb-test | |
+------+-----------+----------+
6 rows in set (0.01 sec)
mysql> delete from user where user='' and host='localhost';
Query OK, 1 row affected (0.01 sec)
mysql> delete from user where user='' and host='adb-test';
Query OK, 1 row affected (0.00 sec)
设置其中root密码为myoa888，并对服务器IP限制
mysql> update user set password=PASSWORD('myoa888'),host='192.168.11.144' where user='root' and host='::1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
对root用户加密
mysql> update user set password=PASSWORD('13C1325E831DEC8D60') where user='root' and host in('localhost','adb-test','127.0.0.1');
mysql> select user,host,password from user;
+------+----------------+-------------------------------------------+
| user | host | password |
+------+----------------+-------------------------------------------+
| root | localhost | *CF51F9E245F41378E51B4154082F26108A5B0D63 |
| root | adb-test | *CF51F9E245F41378E51B4154082F26108A5B0D63 |
| root | 127.0.0.1 | *CF51F9E245F41378E51B4154082F26108A5B0D63 |
| root | 192.168.11.144 | *91AF99F23C3D4ED85140D100433725DFA52BECEE |
+------+----------------+-------------------------------------------+
后续的功能增强：
一、关于数据库交互函数的设置：
①OA数据库设置TD_OA
字符集gbk -- GBK Simplified Chinese
排序规则gbk_chinese_ci
②建立编码表
CREATE TABLE `cs_char2letter` (
`PY` char(1) character set utf8 NOT NULL,
`HZ` char(1) NOT NULL default '',
PRIMARY KEY (`PY`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
③插入
truncate table cs_char2letter;
set names gbk;
insert into cs_char2letter values
('A','骜'),
('B','簿'),
('C','错'),
('D','鵽'),
('E','樲'),
('F','鳆'),
('G','腂'),
('H','夻'),
('J','攈'),
('K','穒'),
('L','鱳'),
('M','旀'),
('N','桛'),
('O','沤'),
('P','曝'),
('Q','囕'),
('R','鶸'),
('S','蜶'),
('T','箨'),
('W','鹜'),
('X','鑂'),
('Y','韵'),
('Z','咗');
④建立函数
DELIMITER $$
DROP FUNCTION IF EXISTS `test`.`func_get_first_letter`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `func_get_first_letter`(words varchar(255)) RETURNS char(1) CHARSET gbk
BEGIN
declare fpy char(1);
declare pc char(1);
declare cc char(4);
set @fpy = UPPER(left(words,1));
set @pc = (CONVERT(@fpy USING gbk));
set @cc = hex(@pc);
if @cc >= "8140" and @cc <="FEA0" then
begin
select PY from cs_char2letter where hz>=@pc limit 1 into @fpy;
end;
end if;
Return @fpy;
END$$
DELIMITER;
报错：
This function has none of DETERMINISTIC, NO SQL解决办法
创建存储过程时
出错信息：
[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)
原因：
这是我们开启了bin-log, 我们就必须指定我们的函数是否是
1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句，当然也不会修改数据
3 READS SQL DATA 只是读取数据，当然也不会修改数据
4 MODIFIES SQL DATA 要修改数据
5 CONTAINS SQL 包含了SQL语句
其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
解决方法：
SQL code
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
1 row in set (0.00 sec)
mysql> set global log_bin_trust_function_creators=1;
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON |
+---------------------------------+-------+
这样添加了参数以后，如果mysqld重启，那个参数又会消失，因此记得在/etc/my.cnf配置文件中添加：
log_bin_trust_function_creators=1
二、对数据库每天进行自动备份，保留15天以内的备份(myisam引擎,innodb数据库引擎不能直接拷贝的方式备份)
1、创建保存mysql数据库备份文件的目录mysqlbakmkdir -pv /backup/mysqlbak
修改属主属组
chown -R mysql.mysql /backup
编辑shell脚本
vim /usr/sbin/bakmysql
加入如下内容：
#关闭mysql数据库服务
service mysqld stop
#创建以当前日期为名称的目录
cd /backup/mysqlbak
time=$(date '+%Y%m%d')
mkdir $time
cp /data/mydata/TD_OA/*.* /backup/mysqlbak/$time
#备份完成后开启mysql数据库服务
service mysqld start
#删除15天以前的备份
find /backup/mysqlbak -type d -mtime +15 -exec rm -rf {} \;
3、修改文件属性，使其可执行
chmod +x /usr/sbin/bakmysql
4、修改/etc/crontab
vim /etc/crontab
#每天3点执行脚本
01 3 * * * mysql /usr/sbin/bakmysql
5、重新启动crond
/etc/rc.d/init.d/crond restart
三、将备份到本机的数据库文件同步备份到其他服务器
安装vsftp服务，并添加对应的ftp用户指向备份文件夹，在数据库备份服务器启用同步计划
四、参数的调整
[client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 default-storage-engine=MyISAM max_connections=1500 socket = /tmp/mysql.sock skip-external-locking key_buffer_size = 4096M max_allowed_packet = 64M table_open_cache = 2400 open_files_limit=65535 tmp_table_size=256M sort_buffer_size = 16M read_buffer_size = 16M read_rnd_buffer_size = 16M myisam_sort_buffer_size = 128M thread_cache_size = 256 query_cache_type=0 query_cache_size= 512M innodb_buffer_pool_size = 1024M skip-name-resolve wait_timeout=300 thread_concurrency = 8 innodb_buffer_pool_instances = 35 innodb_file_per_table = 1 datadir = /data/mydata log-bin=/data/binlogs/mysql-bin binlog-do-db=TD_OA expire_logs_day=30 max_binlog_size = 200M slow_query_log=ON slow-query-log-file=/data/binlogs/slow_query.log long_query_time=2 log_bin_trust_function_creators=1 binlog_format=mixed server-id = 1 [mysqldump] quick max_allowed_packet = 64M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 1024M sort_buffer_size = 512M read_buffer = 256M write_buffer = 256M [mysqlhotcopy] interactive-timeout
记得添加
open_files_limit=65535否则报错如下：
错误#23: Out of resources when opening file './TD_OA/USER.MYD' (Errcode: 24)
SQL语句: SELECT * from USER where USER_ID='admin' or BYNAME='admin'
修改/etc/security/limits.conf，然后加入以下内容，退出再重新登陆即可(不需要重启，退出当前的连接shell即可)
# ulimit -n 查看参数是否生效
#@student - maxlogins 4
* - nproc 1006154
* - nofile 1006154
# End of file
* soft nofile 1006154
* hard nofile 1006154
