1、先在centos上安装mariadb
yum install mariadb mariadb-server
2、设置mariadb开机启动
[root@localhost ~]#systemctl enable mariadb
Created symlink from/etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service
3、准备安装多实例的文件夹
mkdir /usr/dbs
mkdir/usr/dbs/databases
mkdir/usr/dbs/databases/{3307,3308,3308,3310}
mkdir/usr/dbs/{etc,bin,socket}
#为路径授权,以免后期操作出现权限问题
chmod-R 777 /usr/dbs
4、安装多实例
mysql_install_db --basedir=/usr --datadir=/user/dbs/databases/3307 --user=mysql
mysql_install_db --basedir=/usr --datadir=/usr/dbs/databases/3307 --user=mysql
mysql_install_db--basedir=/usr --datadir=/usr/dbs/databases/3308 --user=mysql
mysql_install_db--basedir=/usr --datadir=/usr/dbs/databases/3309 --user=mysql
mysql_install_db--basedir=/usr --datadir=/usr/dbs/databases/3310 --user=mysql
其中的参数--basedir是指定了安装 MySQL 的安装路径,--datadir是指即将安装到的数据库文件目录,如果不知道--basedir该怎么填,可以登录进mysql后查询:
show variables like '%basedir%';
--user是指mysql实例将使用的在linux系统中的用户,最好命名为mysql,yum安装后一般都有这个用户,如果没有可以自主创建:
groupadd mysql
adduser-g mysql mysql
5、查看数据库文件是否安装成功
ls /usr/dbs/databases/3307aria_log.00000001 aria_log_control mysql performance_schema test
如果文件夹中有文件则说明创建成功
6、创建共用配置文件
mkdir /usr/dbs/etc/my.cnf.d/
7、vi /usr/dbs/etc/my.cnf.d/my.cnf
8、
[mysqld]
skip-name-resolve
lower_case_table_names=1innodb_file_per_table=1back_log= 50max_connections= 300max_connect_errors= 1000table_open_cache= 2048max_allowed_packet=16M
binlog_cache_size=2M
max_heap_table_size=64M
sort_buffer_size=2M
join_buffer_size=2M
thread_cache_size= 64thread_concurrency= 8query_cache_size=64M
query_cache_limit=2M
ft_min_word_len= 4
default-storage-engine =innodb
thread_stack=192K
transaction_isolation= REPEATABLE-READ
tmp_table_size=64M
log-bin=mysql-bin
binlog_format=mixed
slow_query_log
long_query_time= 1server-id = 1key_buffer_size=8M
read_buffer_size=2M
read_rnd_buffer_size=2M
bulk_insert_buffer_size=64M
myisam_sort_buffer_size=128M
myisam_max_sort_file_size=10G
myisam_repair_threads= 1myisam_recover
innodb_additional_mem_pool_size=16M
innodb_buffer_pool_size=200M
innodb_data_file_path=ibdata1:10M:autoextend
innodb_file_io_threads= 8innodb_thread_concurrency= 16innodb_flush_log_at_trx_commit= 1innodb_log_buffer_size=16M
innodb_log_file_size=512M
innodb_log_files_in_group= 3innodb_max_dirty_pages_pct= 60innodb_lock_wait_timeout= 120[mysqldump]
quick
max_allowed_packet=256M
[mysql]
no-auto-rehash
prompt=\\u@\\d \\R:\\m>[myisamchk]
key_buffer_size=512M
sort_buffer_size=512M
read_buffer=8M
write_buffer=8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192
有些参数需要根据自己服务器配置进行调整,否则磁盘或者内存可能不够,我是在虚拟机上安装的所以我的
innodb_buffer_pool_size 设置了50M内存,太大了虚拟机跑不起来。
8、创建各个实力的配置文件
vi /usr/dbs/etc/3307.cnf
[client]
port= 3307socket= /usr/dbs/socket/mysql3307.sock
[mysqld]
datadir=/usr/dbs/databases/3307port= 3307socket= /usr/dbs/socket/mysql3307.sock!includedir /home/multiMysql/etc/my.cnf.d
依照上面的格式改变对应端口和路径建立其他配置文件
9、编写数据库启动文件
vi /usr/dbs/bin/mysql.sh
#!/bin/bash
mysql_port=$2mysql_username="root"mysql_password=""function_start_mysql()
{
printf"Starting MySQL...\n"mysqld_safe--defaults-file=/usr/dbs/etc/${mysql_port}.cnf 2>&1 > /dev/null &}
function_stop_mysql()
{
printf"Stoping MySQL...\n"mysqladmin-u ${mysql_username} -p${mysql_password} -S /usr/dbs/socket/mysql${mysql_port}.sock shutdown
}
function_restart_mysql()
{
printf"Restarting MySQL...\n"function_stop_mysql
function_start_mysql
}
function_kill_mysql()
{kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')
}case $1 instart)
function_start_mysql;;
stop)
function_stop_mysql;;kill)
function_kill_mysql;;
restart)
function_stop_mysql
function_start_mysql;;*)esac
赋予可执行权限
chmod +x /usr/dbs/bin/mysql.sh
10、编写启动脚本调用启动文件
vi /usr/dbs/bin/runall.sh
#!/bin/bash/usr/dbs/bin/mysql.sh start 3307
/usr/dbs/bin/mysql.sh start 3308
/usr/dbs/bin/mysql.sh start 3309
/usr/dbs/bin/mysql.sh start 3310
/usr/dbs/bin/mysql.sh start 3311
赋予执行权限
chmod +x /usr/dbs/bin/runall.sh
11、运行 /usr/dbs/bin/runall.sh start
停止的是要输入端口 然后输入密码
/usr/dbs/binmysl.sh stop 3307
尝试连接数据库
mysql -u root -S /usr/dbs/socket/mysql3307.sock
回车直接进入数据
12、赋权远程登录
登录数据库然后执行如下命令:
MariaDB [(none)]> grant all privileges on *.* to 'root'@'%' identified by '123456'with grant option;
MariaDB [(none)]> flush privileges;
为了安全也可以新建用户,不适用root。
13、将脚本加入启动脚本
vi /etc/init.d/runall.sh
#!/bin/bash
# chkconfig:2345 10 90# description: 启动mysql/usr/dbs/bin/mysql.sh start 3307
/usr/dbs/bin/mysql.sh start 3308
/usr/dbs/bin/mysql.sh start 3309
/usr/dbs/bin/mysql.sh start 3310
/usr/dbs/bin/mysql.sh start 3311
chmod +x /etc/init.d/runall.sh
chkconfig --add autostart.shchkconfig autostart.sh on
这样就可以开机启动了
开机启动脚本中
# chkconfig: 2345 10 90# description: 启动mysql 这两行是很重要的没有这两行无法加入自动启动