mysql高可用MHA部署（mysql安装）

从基础开始做起，搭建mysql，这里使用源码编译安装。我这里需要安装3台数据库，然后利用saltstack进行批量安装。

环境：
1、操作系统：CentOS release 6.6 (Final)
2、安装版本： mysql-5.6.31-linux-glibc2.5-x86_64.tar.gz
3、下载地址：http://dev.mysql.com/downloads/mysql/
4、下载说明：上边的下载地址是最新版的，如果想下载老版本可以点击页面中的超链接“Looking for previous GA versions?”

1、下载数据库

选择自己需要的版本，其实我可以选择更新些，为了与先前在使用的版本保持一致，这里选择的是5.6.31

选择版本两个建议：

1.稳定版：选择开源社区版的稳定版GA版本；

2.选择mysql数据库GA版本发布后6个月以上的GA版本

2、检查系统是否有默认的mysql

[[email protected] ~]# rpm -ql mysql
package mysql is not installed

3、批量安装mysql

----------------------------------------------------------------

编辑salt的roster文件

#vi /etc/salt/rostermysql

mysql01:
host: 192.168.166.11
user: root
passwd: Redhat
port: 22
mysql02:
host: 192.168.166.12
user: root
passwd: Redhat
port: 22
manager:
host: 192.168.166.102
user: root
passwd: Redhat
port: 22

----------------------------------------------------------------

上传安装包

salt-ssh --roster-file=‘指定的roster路径’ 主机名操作

#salt-ssh --roster-file='/etc/salt/rostermysql' '*' -r 'yum install lrzsz -y'

#rz

----------------------------------------------------------------

解压,需要等一会

#salt-ssh --roster-file='/etc/salt/rostermysql' '*' -i -r 'cd /usr/local/src && tar -zxvf mysql-5.6.31-linux-glibc2.5-x86_64.tar.gz'

将解压后文件移动至到安装目录，并改名

#salt-ssh --roster-file='/etc/salt/rostermysql' '*' -i -r 'mv /usr/local/src/mysql-5.6.31-linux-glibc2.5-x86_64 /usr/local/mysql'

增加用户组和用户

#salt-ssh --roster-file='/etc/salt/rostermysql' '*' -i -r 'groupadd mysql && useradd -r -g mysql mysql'

修改mysql目录的所属组

#salt-ssh --roster-file='/etc/salt/rostermysql' '*' -i -r 'chown -R mysql:mysql /usr/local/mysql'

----------------------------------------------------------------

创建数据库目录

#salt-ssh --roster-file='/etc/salt/rostermysql' '*' -i -r 'mkdir -p /data/mysql3306/data'

修改目录权限

#salt-ssh --roster-file='/etc/salt/rostermysql' '*' -i -r 'chown -R mysql:mysql /data'

----------------------------------------------------------------

创建my.cnf文件，并复制至其余两台

vi my3306.cnf

[client]
port = 3306
socket = /data/mysql3306/tmp/mysql3306.sock

[mysql]
auto-rehash

[mysqld_safe]
log-error = /data/mysql3306/my3306.err
pid-error = /data/mysql3306/my3306.pid

[mysqld]
user = mysql
port = 3306
socket= /data/mysql3306/tmp/mysql3306.sock
pid-file = /data/mysql3306/mysql3306.pid
datadir = /data/mysql3306/data
basedir = /usr/local/mysql

skip_name_resolve = 1
skip-external-locking
max_connections = 3000
max_connect_errors = 10
transaction_isolation = READ-COMMITTED
interactive_timeout=86400
wait_timeout=86400
back_log=600
####cache######
table_open_cache=2000
thread_cache_size=500
query_cache_size=128M
query_cache_min_res_unit=128k
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=4M
join_buffer_size=2M
bulk_insert_buffer_size=16M
max_heap_table_size=64M
tmp_table_size=64M

###MyISAM####
key_buffer_size=64M
key_cache_block_size=4k
myisam_sort_buffer_size=2M

########innodb settings########
#innodb_page_size = 4k
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 1
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
#innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 50
#innodb_io_capacity = 4000
#innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_log_group_home_dir = /data/mysql3306/redolog/
innodb_undo_directory = /data/mysql3306/undolog/
innodb_undo_logs = 128
#innodb_undo_tablespaces = 3
innodb_flush_neighbors = 1
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 4M

# scp my3306.cnf 192.168.66.11:/data/mysql3306/

# scp my3306.cnf 192.168.66.12:/data/mysql3306/

# chown mysql:mysql my3306.cnf

----------------------------------------------------------------

创建相关目录，初始化mysql

#cd /data/mysql3306

#mkdir data tmp redolog undolog

#chown -R mysql:mysql /data

#salt-ssh --roster-file='/etc/salt/rostermysql' '*' -i -r 'cd /usr/local/mysql/scripts && ./mysql_install_db --user=mysql --basedir=/usr/local/mysql --defaults-file=/data/mysql3306/my3306.cnf'

启动mysql

#salt-ssh --roster-file='/etc/salt/rostermysql' '*' -i -r 'cd /usr/local/mysql/bin && ./mysqld_safe --defaults-file=/data/mysql3306/my3306.cnf & '

查看是否启动

ps -ef | grep mysql

关闭mysql

#cd /usr/local/mysql/bin && ./mysqladmin -S /data/mysql3306/tmp/mysql3306.sock -uroot -p shutdown

----------------------------------------------------------------

设置启停快捷方式

[[email protected] mysql3306]# cat start_mysql.sh
#!/bin/sh
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql3306/my3306.cnf &

[[email protected] mysql3306]# cat stop_mysql.sh
#!/bin/sh
/usr/local/mysql/bin/mysqladmin -S /data/mysql3306/tmp/mysql3306.sock -uroot -p shutdown

#chmod 777 start_mysql.sh stop_mysql.sh

#chown mysql:mysql start_mysql.sh stop_mysql.sh

----------------------------------------------------------------

登录mysql快捷方式

[[email protected] mysql3306]# cat login_mysql.sh
#!/bin/sh
/usr/local/mysql/bin/mysql -uroot -p -S /data/mysql3306/tmp/mysql3306.sock

[[email protected] mysql3306]# chown mysql:mysql login_mysql.sh
[[email protected] mysql3306]# chmod 777 login_mysql.sh

----------------------------------------------------------------

mysql账户安全设置

mysql> select user,host,password from mysql.user;

目前这里只保留root localhost 的

mysql> delete from mysql.user where user='';

mysql> delete from mysql.user where host <>'localhost';

为root创建密码

mysql> set password for [email protected]'localhost' = password('XXXXXX');

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

【完】

参考文档:

Linux下安装MySQL5.6
Linux 下安装 mysql5.6.30

centos6.5 mysql5.6.24 单机多实例安装

mysql高可用MHA部署（mysql安装）

相关推荐