由于公司设备对数据需要大量的读取和查询,开始使用的mysql8,但是未优化的mysql插入和查询及其缓慢,因此我与同事分开研究,优化方案。我负责寻找可替代高效的数据库,然后问同学,发现alisql性能不错,打算研究下,安装后发现相同未配置情况下,alisql可以快mysql起码十倍。
注:由于我们程序内存在长索引,但是由于5.6.32基于的mysql版本过老,无法加长索引,故放弃,但是用来一般毕业设计或者没有场索引需求的产品,我认为这是个绝佳的选择。
如果程序内有一些必须新版mysql才能实现的功能,建议直接放弃此版本数据库。
安装步骤
1.系统准备
安装centos7时,选择带有桌面服务器,软件选择中必选“开发工具”(如果不选开发软件,gcc之类的都是默认不装的,安装起来需要各种依赖很麻烦)
2、下载准备需要使用的安装包
ncurses-5.9.tar.gz
cmake-2.8.10.2.tar.gz
bison-2.7.tar.gz
AliSQL-AliSQL-5.6.32-8.tar.gz
my.cnf(此文件为数据库配置)
以上为所需要的安装包,自行去网上下载一下。
或者使用百度网盘:链接: https://pan.baidu.com/s/1VMMH_ZoiaAGRK_pcZYjlAA 提取码: awpi
将所有安装包和文件拖入到/opt/alisql/下,这个文件夹需要自己创建。
my.cnf文件内容:
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.[mysqld]# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
disable_log_bin# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 10M
sort_buffer_size = 10M
# read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
lower_case_table_names = 1
执行以下脚本:
#!/bin/bash
echo "uninstall alisql ......"
cd /opt/alisql/tar -zxf ncurses-5.9.tar.gz
cd /opt/alisql/ncurses-5.9/
./configure
cd /opt/alisql/ncurses-5.9/
make
make install
echo "install ncurses end "cd /opt/alisql/tar zxf cmake-2.8.10.2.tar.gz
cd cmake-2.8.10.2
./bootstrap
make
make install
echo "install cmake end ...... "sleep 2cd /opt/alisql/
tar zxf bison-2.7.tar.gz
cd bison-2.7
./configure
make -j 8
make install
echo "install bison end ...... "
sleep 2cd /opt/alisql/
groupadd mysql
useradd -r -g mysql mysql
mkdir -p /data/mysqldb
mkdir -p /opt/install
mkdir -p /opt/install/mysql
tar zxvf AliSQL-AliSQL-5.6.32-8.tar.gz
cd /opt/alisql/AliSQL-AliSQL-5.6.32-8/
cmake -DCMAKE_INSTALL_PREFIX=/opt/install/mysql -DMYSQL_UNIX_ADDR=/opt/install/mysql/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DMYSQL_DATADIR=/data/mysqldb -DMYSQL_TCP_PORT=3306 -DENABLE_DOWNLOADS=1
cd /opt/alisql/AliSQL-AliSQL-5.6.32-8/
make
make installecho "change group ... "cd /opt/install/mysql
chown -R mysql:mysql .
cd /data/mysqldb
chown -R mysql:mysql .echo "init alisql ... "cd /opt/install/mysql/
scripts/mysql_install_db --user=mysql --datadir=/data/mysqldbecho "cpoy my.cnf mysqld ... "
cp /opt/alisql/my.cnf /etc/my.cnf
cp support-files/mysql.server /etc/init.d/mysqldecho "change path ... "
sed -i '54i\export PATH=/opt/install/mysql/bin:/opt/install/mysql/lib:$PATH' /etc/profileecho "restart path ... "
sleep 1
source /etc/profileecho "start mysqld ... "
service mysqld startecho "Power on start mysqld ... "
chkconfig --level 35 mysqld onecho "stop firewalld ... "
systemctl stop firewalldecho "install alisql end ... "sleep 1
完成后通过netstat指令查看3306端口是否开启,开启即为成功,若失败自行排查问题。
进行数据库配置和开启远程连接进行如下操作:
mysql -u root -p
use mysql;
select host,user, password from user;
update user set password=password('mypasswd'), host= '%' where user ='root' and host='127.0.0.1';
update user set password=password('mypasswd') where user ='root' and host='localhost';
flush privileges;
默认安装本地root登录无密码,第五行的修改语句将本地root登录修改为自己的密码。
希望以上对初入alisql的人有帮助!