SUSE系统下数据库源码安装以及mysql主从复制指引(多实例)

1、中麦通讯业务支撑系统(BSS)数据库实施手册1.项目目标32.总体设计32.1主机设备32.2主机数据库IP43.环境准备43.1操作系统43.1.1数据库主机OS补丁要求43.1.2主机参数43.1.3文件系统划分53.1.4用户设计53.2数据库软件63.2.1数据库补丁要求63.2.2数据库安全补丁要求63.3其他软件环境:63.4硬件环境63.5网络需求64数据库安装配置64.1安装64.2半同步配置74.3配置文件（多实例）74.4数据库同步账号及监控账号配置104.5数据同步配置115.MHA安装配置125.1安装MHA125.2配置MHA Manager135.3配置Linux用

2、户145.4配置MySQL用户145.5配置VIP切换脚本156.MHA检查命令及参考输出176.1MHA检查SSH配置masterha_check_ssh176.2检查主从配置masterha_check_repl186.3启动MHA Manager206.4检查MHA运行状态masterha_check_status206.5手工切换主从masterha_master_switch206.6关闭MHA Manager命令masterha_stop256.7删除过期的中继日志purge_relay_logs266.8主从切换的日志267.数据库实例配置327.1数据库server配置327.

3、2权限设定327.2.1角色规划327.2.2用户及权限328.安全加固328.1删除默认用户，修改root密码328.2用户DDL权限控制328.3禁止域名解析338.4按程序和服务器功能严格控制用户权限338.5禁止在Linux命令行中明文输入任何用户名及密码；338.6控制访问权限保护数据库文件安全；331. 项目目标中麦通讯业务支撑系统预计到2015年底支撑300万用户数，包括CRM、计费、账务、接口、微厅、短厅等业务功能模块。将系统建设成高可用、高可靠、高可维护、高稳定性系统。2. 总体设计为了系统资源的高可用性以及数据库的高冗余性，所以采用主流MHA，在该环境中，MySQL为主从关

4、系，这样就保证了两台MySQL数据的一致性，用MHA提供业务虚拟IP，通过MHA自带的服务监控功能来实现MySQL故障时自动切换。结构分布图如下：2.1主机设备设备型号IP说明DB1HP DL580 G7192.168.100.24CPU/64GRAMDB2HP DL580 G7192.168.100.34CPU/64GRAM2.2主机数据库IP主机名类型IPDB-PC-HP-DL580G7-1主机IP192.168.100.2DB-PC-HP-DL580G7-2主机IP192.168.100.3vip192.168.100.13. 环境准备3.1操作系统3.1.1数据库主机OS补丁要求数据库

5、主机操作系统采用的是SUSE Linux Enterprise Server 11 SP2  (x86_64) 版本。支持Mysql 5.6.15（社区版）数据库软件安装，无需安装OS补丁。3.1.2主机参数为了保证Mysql的正常运行，需要调整部分OS内核参数。参数调整时，需要考虑以下情况：为了能在其中一台数据库主机出现故障时，另外一台主机能临时接管所有的应用连接。具体参数调整建议如下：#vi /etc/sysctl.confkernel.shmall = 2097152kernel.shmmax = 2147483648kernel.shmmni = 4096kernel.sem

6、 = 250 32000 100 128fs.file-max = 65536net.ipv4.ip_local_port_range = 9000 65000net.core.rmem_default = 4194304net.core.rmem_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 262144#sysctl p 使生效3.1.3文件系统划分在/目录下创建：文件系统名称Vg文件系统大小（G）文件系统使用说明/dataDatavg300用于存放数据库文件/logLogvg100用于存放数据库日志文件3.1

7、.4用户设计用户设计原则如下：Ø 用户分层至少包含系统管理（超级用户）、数据属主、程序运行、维护监控等用户层次，不同的平台软件、应用软件及维护人员使用不同的用户。Ø 权限与角色l 权限分层：分为系统权限和数据权限，根据系统管理和应用程序实际需要赋予相应权限l 最小权限原则：对于数据库用户，赋予满足其要求的最小的权限集l 普通用户不得授予DBA角色l 普通用户尽量不赋予“ANY”权限Ø 用户口令策略l 口令有效期：90 天l 口令失效后第一次用它成功登录之后 5天内可以更改此口令l 口令历史保留纪录 5 次l 口令历史保留时间 180 天l 口令复杂性-长度大于等于

8、 8l 口令复杂性-必需包含字母、数字、字符l 登陆失败 5 次之后锁定用户l 登陆失败锁定用户时间：7200天3.2数据库软件3.2.1数据库补丁要求截至2014-2-25日，暂无建议安装的MySQL Community Server 5.6.15的补丁。3.2.2数据库安全补丁要求截至2014-2-25日，暂无建议安装的MySQL Community Server 5.6.15的安全补丁。3.3其他软件环境: 操作系统：SUSE Linux Enterprise Server 11 SP2  (x86_64)高可用软件： keepalived-1.2.9数据库：Mysql 5.6

9、.15（社区版） 3.4硬件环境内存空间需求 至少64G物理内存3.5网络需求1. 每个节点必须有至少4个光口+4个电口2. 分别做公有和私有接口3、网口必须一致，如两台主机均为ETH0。4数据库安装配置4.1安装SUSE系统默认安装mysql 5.0.94，请先卸载。rpm qa|grep mysqlrpm -e mysql-4.1.20-2.RHEL4.1.i386 -nodeps建mysql用户mysql组#useradd m mysql#groupadd mysql#usermod g mysql mysql修改文件所属组chown -R mysql:mysql /datachown

10、-R mysql:mysql /logmkdir /usr/local/mysqlchown R mysql:mysql /usr/local/mysql安装编译软件cmake(mysql5.5以后都是通过cmake来编译的)#tar -zxv -f cmake-2.8.5.tar.gz#cd cmake-2.8.5#./configure（重新编译时，需要清除旧的对象文件和缓存信息 # make clean # rm -f  CMakeCache.txt）#make#make install从官网下载软件包mysql-5.6.15.tar.gz，上传到主机上进行

11、解压：#tar zxvf mysql-5.6.15.tar.gz#cd mysql-5.6.15cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc -DMYSQL_DATADIR=/data -DCOMMUNITY_BUILD=ON -DFEATURE_SET=community -DENABLED_PROFILING=ON -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=

12、1 -DWITHOUT_PERFSCHEMA_STORAGE_ENGINE=0 -DENABLED_LOCAL_INFILE=1 -DMYSQL_TCP_PORT=5393 -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DWITH_SSL=bundled -DWITH_EMBEDDED_SERVER=OFF -DWITH_READLINE=1 -DWITH_DEBUG=ON -DENABLE_DOWNLOADS=0#make -j8#make install4.2半同步

13、配置MHA需要半同步配置来辅助。半同步插件是随着源码发行和自动编译的，所以我们只需要把它加载起来，再配置好即可。首先在运行起来的MySQL实例里运行如下命令加载插件：INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'然后就是配置。在f文件中加入如下三行即可（已在配置中体现）：rpl_semi_sync_master_enabled=1rpl_semi_sync_mas

14、ter_timeout=1000 # 1 secondrpl_semi_sync_slave_enabled=14.3配置文件（多实例）主服务器：vi /etc/fmysqld_multimysqld = /usr/local/mysql/bin/mysqld_safesocket = /data/dbdata_5393/mysql.sockmysqladmin = /usr/local/mysql/bin/mysqladminuser = mysqlpassword = Mysql12#mysqldsocket = /data/dbdata_5393/mysql.sockport = 539

15、3server-id=1log-bin=/log/log_5393/binlogskip-federatedmysqld1socket = /data/dbdata_5393/mysql.sockport = 5393pid-file = /data/dbdata_5393/5393.piddatadir = /data/dbdata_5393log_bin= /log/log_5393/binloglog_bin_index=/log/log_5393/binlog_indexmax_binlog_size=100Mlog_error=/log/log_5393/error.loggener

16、al_log=1general_log_file=/log/log_5393/general.logtmpdir= /data/tmpsync_binlog=1slave_skip_errors=10,071,062master-info-repository=TABLErelay_log=/mnt/disk1/mysql_6708_binlog/relaylogrelay_log_index=/mnt/disk1/mysql_6708_binlog/relay.indexrelay_log_purge=0max_relay_log_size=100Muser = mysqlskip-name

17、-resolveskip-host-cacheskip-external-lockinglog_output=FILElower_case_table_names=1innodb_file_per_table=1back_log = 50max_connections = 200max_connect_errors = 30table_open_cache = 512max_allowed_packet = 32Mbinlog_cache_size = 32Mmax_heap_table_size = 128Msort_buffer_size = 256Mjoin_buffer_size =

18、8Mthread_cache_size = 64thread_concurrency = 32query_cache_size = 0query_cache_type=1query_cache_limit = 2Mft_min_word_len = 4default-storage-engine = innodbthread_stack = 192Ktransaction_isolation = REPEATABLE-READkey_cache_block_size=1024tmp_table_size = 64Mlog-bin=mysql-binbinlog_format=rowslow_q

19、uery_log=onexpire_logs_days=7log-slow-queries= /log/log_5393/slow.logslow_query_log_file= /log/log_5393/slow.loglong_query_time = 1server-id = 1key_buffer_size = 512Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 256Mmyisam_max_sort_file_size = 1

20、00Gmyisam_repair_threads = 1myisam_recoverinnodb_flush_log_at_trx_commit = 1innodb_buffer_pool_size = 40Ginnodb_flush_log_at_timeout=1innodb_log_buffer_size = 16Minnodb_log_file_size = 256Minnodb_log_files_in_group = 4innodb_thread_concurrency = 16innodb_additional_mem_pool_size = 16Minnodb_data_fil

21、e_path = ibdata1:10M:autoextendinnodb_file_io_threads = 8innodb_max_dirty_pages_pct = 60innodb_lock_wait_timeout = 120#binlog-do-db = 数据库名 （要备份的数据库）#binlog-ignore-db = 数据库名 （不需要备份的数据库）log-slave-updatesrelay-log=hostname-relay-binrpl_semi_sync_master_enabled=1rpl_semi_sync_master_timeout=1000 # 1 sec

22、ondrpl_semi_sync_slave_enabled=1mysqld1mysqld3mysqldumpquickmax_allowed_packet = 256Mmysqlno-auto-rehashprompt=ud R:m>myisamchkkey_buffer_size = 512Msort_buffer_size = 512Mread_buffer = 8Mwrite_buffer = 8Mmysqlhotcopyinteractive-timeoutmysqld_safeopen_files_limit=2048从服务器：与主服务器基本一致，修改server-id =

23、2即可。配置环境变量#vi /etc/profilePATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/lib建库：/usr/local/mysql/scripts/mysql_install_db -user=mysql -basedir=/usr/local/mysql/ -datadir=/data启动数据库，判断是否安装成功#su mysql#cd /usr/local/mysql/bin#./mysqld_safe user=mysql &#ps ef |grep mysql#mysql u root mysqlmysqld_sa

24、fe -user=mysql -skip-grant-tables查看状态及启停：mysqld_multi -defaults-file=./etc/f reportmysqld_multi -defaults-file=./etc/f start 1mysqld_multi -defaults-file=./etc/f stop 14.4数据库同步账号及监控账号配置在192.168.100.2启动mysql服务器登录mysql服务器，添加一个叫做同步用户，并授权给从服务器如果/data/dbdata_5393目录下不存在mysql.sock，并确认/tmp目录下存在mys

25、ql.sock，执行：#ln -s /tmp/mysql.sock /data/dbdata_5393/mysql.sock# /usr/local/mysql/bin/mysql -u mysql -p -S /data/dbdata_5393/mysql.sock#创建suser用户，密码设置为Zmtx12#，并授权给192.168.100.3使用。mysql> grant replication slave on *.* to 'suser''192.168.100.3' identified by ' Zmtx12#' mysql&

26、gt;flush privileges; # 刷新mysql>exit在192.168.100.3启动mysql服务器登录mysql服务器，添加一个叫做同步用户，并授权给从服务器# /usr/local/mysql/bin/mysql u mysql -p -S /data/dbdata_5393/mysql.sock#创建suser用户，密码设置为Zmtx12#，并授权给192.168.100.2使用。mysql> grant replication slave on *.* to 'suser''192.168.100.2' identified

27、 by ' Zmtx12#' mysql>flush privileges; # 刷新mysql>exit4.5数据同步配置在192.168.100.2上登录mysql：# /usr/local/mysql/bin/mysql u mysql -p -S /data/dbdata_5393/mysql.sockmysql> show master status;+-+-+-+-+-+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-+-+-+-+-+| mysq

28、l-bin.000001 | 120 | | | |+-+-+-+-+-+1 row in set (0.03 sec)记下File和Position的值在192.168.100.3上登录mysql：# /usr/local/mysql/bin/mysql u mysql -p -S /data/dbdata_5393/mysql.sockmysql> change master to master_host='192.168.28.137',master_user='suser',master_password='123456#',mas

29、ter_log_file=' binlog.000011',master_log_pos=120;Query OK， 0 rows affected， 2 warnings (0.24 sec)启动slavemysql> start slave;Query OK， 0 rows affected (0.06 sec)检查同步状态，加黑字体为Yes，同步状态正常mysql> show slave statusG* 1. row * Slave_IO_State: Waiting for master to send event Master_Host: 192.168

30、.100.2 Master_User: suser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 120 Relay_Log_File: 192.168.100.3-relay-bin.000002 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_I

31、gnore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: .5.MHA安装配置MHA是一套MySQL故障切换方案，来保证数据库系统的高可用.在宕机的时间内（通常1030秒内），完成故障切换，部署MHA，可避免主从一致性问题，不影响服务器性能，易安装，不改变现有部署。有点如下：1、 master自动监控和故障转移在当前已存在的主从复制环境中，MHA可以监控master主机故障，并且故障自动转移。2、 互动(手动)master故障转移MHA可以用来只做故障转移，而不监测master，MHA只作为故障

32、转移的交互。3、 在线切换master到不同主机1235.1安装MHA安装MHA过程为先卸载掉系统自带的Perl-5.10，再安装Perl-5.12.3，再安装MHA相关软件包。对于MHA Node节点，安装到Node完成即可。对于MHA Manager节点，要全部安装。rpm -e perl-base-5.10.0-64.55.1 nodepsrpm -e perl-5.10.0-64.55.1 nodeps执行 chkconfig pliance offrpm -ivh perl-base-5.12.3-11.14.1.x86_64.rpm rpm -ivh libdb-4_8-4.8.3

33、0-2.4.x86_64.rpmrpm -U zlib-1.2.5-8.1.x86_64.rpmrpm -ivh perl-5.12.3-11.14.1.x86_64.rpmrpm -e perl-DBI-1.607-1.16.x86_64 -nodepsrpm -ivh perl-DBI-1.616-4.1.x86_64.rpmrpm -ivh perl-Data-ShowTable-3.3-719.1.x86_64.rpmrpm -ivh libopenssl1_0_0-1.0.1e-5.1.x86_64.rpmrpm -ivh libmysqlclient16-5.1.67-58.1.x

34、86_64.rpm rpm -ivh perl-DBD-mysql-4.018-4.2.x86_64.rpmrpm -ivh mha4mysql-node-0.54-1.el5.noarch.rpm node成功 rpm -ivh perl-Config-Tiny-2.13-3.1.noarch.rpm rpm -ivh perl-Params-Validate-0.95-8.1.x86_64.rpmrpm -ivh perl-Log-Dispatch-2.29-21.1.noarch.rpm rpm -ivh perl-Parallel-ForkManager-0.7.9-1.1.noarc

35、h.rpm rpm -ivh mha4mysql-manager-0.55-1.el5.noarch.rpm manager成功安装mha4mysql-manager-0.54.tar.gz#perl Makefile.Pl#make#make install然后还有点库路径配置，主要是为了解决下面的错：masterha_check_ssh -conf=/etc/masterha/f Can't locate MHA/SSHCheck.pm in INC (INC contains: /usr/lib/perl5/site_perl/5.12.3/x86_64-linux-thread

36、-multi /usr/lib/perl5/site_perl/5.12.3 /usr/lib/perl5/vendor_perl/5.12.3/x86_64-linux-thread-multi /usr/lib/perl5/vendor_perl/5.12.3 /usr/lib/perl5/5.12.3/x86_64-linux-thread-multi /usr/lib/perl5/5.12.3 .) at /usr/bin/masterha_check_ssh line 25.BEGIN failed-compilation aborted at /usr/bin/masterha_c

37、heck_ssh line 25.解决办法如下：ln -s /usr/lib/perl5/vendor_perl/MHA /usr/lib/perl5/vendor_perl/5.12.3/MHA5.2配置MHA Managermkdir /etc/masterhamkdir -p /masterha/app1cp samples/conf/* /etc/masterha/cat /etc/masterha/f server defaultmanager_workdir=/masterha/app1manager_log=/masterha/app1/manager.loguser=

38、mhapassword=mhaport=5393ssh_user=mharepl_user=suserrepl_password=fqrewqfRFEQWping_interval=1shutdown_script="" master_ip_failover_script="/usr/local/bin/master_ip_failover" master_ip_online_change_script="/usr/local/bin/master_ip_online_change"report_script=""

39、 server1hostname=192.168.100.2master_binlog_dir="/log/log_5393"candidate_master=1server2hostname=192.168.100.3master_binlog_dir="/log/log_5393"candidate_master=1配好后，所有日志都在文件“/masterha/app1/manager.log”中。5.3配置Linux用户在两台服务器上都要配相同的Linux用户，并且加入sudoer，配公钥。创建用户：useradd -s /bin/bash -d

40、/home/mha -m mhapasswd mhamha!1234加入sudoer：vi /etc/sudoers在“root ALL=(ALL) ALL”后面加一行：mha             ALL=NOPASSWD:ALL注释掉Defaults    requiretty这一行，因为SSH过来没有TTY。将mha用户加入mysql用户组：usermod -A mysql mha增加相关目录及修改权限：mkdir p /mas

41、terha/app1chown -R mha /masterha/app1mkdir -p /var/tmpchown -R  mha /var/tmpchmod 775 R /log配密钥，在两台机器上都生成密钥，然后传到另其他服务器上。su mhassh-keygen -t rsassh-copy-id -i /.ssh/id_rsa.pub mha192.168.100.2 ssh-copy-id -i /.ssh/id_rsa.pub mha192.168.100.35.4配置MySQL用户每个MySQL实例都要向两台机器做如下授权，一是MHA

42、Manager，在这里是“192.168.100.2”，二是本机。grant reload, replication client, super on *.* to mha'192.168.100.2' identified by 'mha'grant select,create,insert,update,delete,drop on mysql.* to mha'192.168.100.2' identified by 'mha'flush privileges;5.5配置VIP切换脚本相应的处理脚本可以内容相同保存两份，为“

43、/usr/local/bin/master_ip_failover”。并且记得“chmod +x”。#!/usr/bin/env perluse strict;use warnings FATAL => 'all'use Getopt:Long;my ( $command, $ssh_user, $orig_master_host, $orig_master_ip, $orig_master_port, $new_master_host, $new_master_ip, $new_master_port, $new_master_user, $new_master_pas

44、sword );my $vip = '192.168.108.66' # Virtual IPmy $key = "1"my $ssh_start_vip = "sudo /sbin/ifconfig eth0:$key $vip"my $ssh_stop_vip = "sudo /sbin/ifconfig eth0:$key down"GetOptions( 'command=s' => $command, 'ssh_user=s' => $ssh_user, '

45、orig_master_host=s' => $orig_master_host, 'orig_master_ip=s' => $orig_master_ip, 'orig_master_port=i' => $orig_master_port, 'new_master_host=s' => $new_master_host, 'new_master_ip=s' => $new_master_ip, 'new_master_port=i' => $new_master_p

46、ort, 'new_master_user=s' => $new_master_user, 'new_master_password=s' => $new_master_password, );exit &main();sub main print "nnIN SCRIPT TEST=$ssh_stop_vip=$ssh_start_vip=nn" if ( $command eq "stop" | $command eq "stopssh" ) # $orig_master_hos

47、t, $orig_master_ip, $orig_master_port are passed.# If you manage master ip address at global catalog database,# invalidate orig_master_ip here. my $exit_code = 1; eval print "Disabling the VIP on old master: $orig_master_host n" &stop_vip(); $exit_code = 0; ; if ($) warn "Got Erro

48、r: $n" exit $exit_code; exit $exit_code; elsif ( $command eq "start" ) # all arguments are passed.# If you manage master ip address at global catalog database,# activate new_master_ip here.# You can also grant write access (create user, set read_only=0, etc) here. my $exit_code = 10;

49、eval print "Enabling the VIP - $vip on the new master - $new_master_host n" &start_vip(); $exit_code = 0; ; if ($) warn $; exit $exit_code; exit $exit_code; elsif ( $command eq "status" ) print "Checking the Status of the script. OK n" #ssh $ssh_usercluster1 "

50、$ssh_start_vip " exit 0; else &usage(); exit 1; # A simple system call that enable the VIP on the new mastersub start_vip() ssh $ssh_user$new_master_host " $ssh_start_vip "# A simple system call that disable the VIP on the old_mastersub stop_vip() ssh $ssh_user$orig_master_host &q

51、uot; $ssh_stop_vip "sub usage print "Usage: master_ip_failover -command=start|stop|stopssh|status -orig_master_host=host -orig_master_ip=ip -orig_master_port=port -new_master_host=host -new_master_ip=ip -new_master_port=portn"/usr/local/bin/master_ip_online_change：#!/usr/bin/env perluse strict;use warnings FATAL => 'all'use Getopt:Long;use MHA:DBHelper;my ( $command, $ssh_user, $orig_master_host, $orig_master_ip, $orig_master_port, $new_master_host, $new_master_ip, $new_master_port, $new_master_user, $new_master_password);my $vip = '19