3月 072017
 

 

[mysqld]
ssl-ca=/etc/mysql-ssl/ca-cert.pem
ssl-cert=/etc/mysql-ssl/server-cert.pem
ssl-key=/etc/mysql-ssl/server-key.pem
ssl-cipher=AES128+EECDH:AES128+EDH
# replication:
GRANT REPLICATION SLAVE ON *.* to ‘repl’@’%’ REQUIRE SSL;
STOP SLAVE;
CHANGE MASTER MASTER_SSL=1,
MASTER_SSL_CA=’/etc/mysql-ssl/ca-cert.pem’,
MASTER_SSL_CERT=’/etc/mysql-ssl/client-cert.pem’,
MASTER_SSL_KEY=’/etc/mysql-ssl/client-key.pem';
SHOW SLAVE STATUS\G;
START SLAVE;
SHOW SLAVE STATUS\G;

内容引用:
(1)cipherlist

5月 052016
 

在CentOS 6.7下安装MySQL 5.6版本

下载并安装官方Yum源

[root@localhost ~]# yum -y install http://dev.mysql.com/get/mysql57-community-release-el6-8.noarch.rpm

查看RPM安装包路径

[root@localhost ~]# rpm -lq mysql57-community-release-el6-8
/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
/etc/yum.repos.d/mysql-community-source.repo
/etc/yum.repos.d/mysql-community.repo
[root@localhost ~]#

查看并修改mysql-community.repo文件
启用(enable=1)需要安装的版本,禁用(enable=0)不需要的版本

[root@localhost ~]# vi /etc/yum.repos.d/mysql-community.repo
[mysql-connectors-community]
name=MySQL Connectors Community
baseurl=http://repo.mysql.com/yum/mysql-connectors-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-community]
name=MySQL Tools Community
baseurl=http://repo.mysql.com/yum/mysql-tools-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# Enable to use MySQL 5.5
[mysql55-community]
name=MySQL 5.5 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.5-community/el/6/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# Enable to use MySQL 5.6
[mysql56-community]
name=MySQL 5.6 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-preview]
name=MySQL Tools Preview
baseurl=http://repo.mysql.com/yum/mysql-tools-preview/el/6/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

查看仓库列表的启用及禁用状态

mysql-yum-repo-quick-guide-01

更新本地cache后查看mysql-community-server版本信息mysql-yum-repo-quick-guide-02

安装

相关引用:

MySQL Yum repository supports the following Linux Distros:

Red Hat Enterprise Linux 7 / Oracle Linux 7
Red Hat Enterprise Linux 6 / Oracle Linux 6
Red Hat Enterprise Linux 5 / Oracle Linux 5
Fedora 21, 22 and 23

MySQL Yum repository includes the latest packages:

 MySQL 5.7 (GA)
 MySQL 5.6 (GA)
 MySQL 5.5 (GA - Red Hat Enterprise Linux and Oracle Linux Only)
 MySQL Workbench
 MySQL Fabric
 MySQL Router
 MySQL Utilities
 MySQL Connector / ODBC
 MySQL Connector / Python
 MySQL Shell (preview)

下载地址
http://dev.mysql.com/downloads/repo/yum/

官方指南
http://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/

10月 252014
 

复制状态检测插件

[root@monitor libexec]# ./check_mysql_slavestatus.sh -H 192.168.153.113 -P 3306 -u cacti -p qweszxc -w 2 -c 5
 OK: Slave SQL running: Yes Slave IO running: Yes / master: 192.168.153.112 / slave is 0 seconds behind master |
 delay=0s
[root@monitor libexec]#

取值来源

Slave_SQL_Running
Slave_IO_Running
Master_Host
Seconds_Behind_Master

新增插件
http://www.claudiokuenzler.com/nagios-plugins/check_mysql_slavestatus.sh

定义命令

[root@monitor libexec]# vi ../etc/objects/commands.cfg
define command{
 command_name check_mysql_slavestatus
 command_line $USER1$/check_mysql_slavestatus.sh -H $HOSTADDRESS$ -P $ARG1$ -u $ARG2$ -p $ARG3$
 }

在配置文件中引用命令

define service{
 use generic-service
 host_name dbmaster01
 service_description MySQL Slave Status
 check_command check_nrpe!check_mysql_slavestatus!3306!cacti!qweszxc!1!5
 }

优化,重定义(适用多台相同账户配置主机)

# 'check_mysql_slavestatus'
define command{
 command_name check_mysql_slavestatus
 command_line $USER1$/check_mysql_slavestatus.sh -H $HOSTADDRESS$ -P 3306 -u cacti -p qweszxc -w
 $ARG1$ -c $ARG2$
 }
define service{
 use generic-service
 host_name dbmaster01
 service_description MySQL Slave Status
 check_command check_mysql_slavestatus!1!5
 }

nagios-check-mysql-slavestatus

Seconds_Behind_Master 解析

http://blog.itpub.net/23721637/viewspace-1050178/

MySQL slave状态之Seconds_Behind_Master

http://blog.csdn.net/zbszhangbosen/article/details/8494921

8月 222014
 

服务器主机IP和虚拟浮动IP配置

RealServer A 192.168.75.133
RealServer B 192.168.75.134
VIP A 192.168.75.110
VIP B 192.168.75.111

安装KeepAlived软件包

[root@mysql-b ~]# yum install keepalived

================================================================================
 Package Arch Version Repository Size
================================================================================
 Installing:
 keepalived i686 1.2.7-3.el6 base 170 k
 Installing for dependencies:
 libnl i686 1.1.4-2.el6 base 124 k
 lm_sensors-libs i686 3.1.1-17.el6 base 37 k
 net-snmp-libs i686 1:5.5-49.el6_5.1 updates 1.5 M
 perl i686 4:5.10.1-136.el6 base 9.7 M
 perl-Module-Pluggable i686 1:3.90-136.el6 base 40 k
 perl-Pod-Escapes i686 1:1.04-136.el6 base 32 k
 perl-Pod-Simple i686 1:3.13-136.el6 base 212 k
 perl-libs i686 4:5.10.1-136.el6 base 593 k
 perl-version i686 3:0.77-136.el6 base 51 k
Transaction Summary
================================================================================
Install 10 Package(s)

查看RPM包的文件路径

[root@mysql-a ~]# rpm -lq keepalived
 /etc/keepalived
 /etc/keepalived/keepalived.conf
 /etc/rc.d/init.d/keepalived
 /etc/sysconfig/keepalived
 /usr/bin/genhash
 /usr/sbin/keepalived
 /usr/share/doc/keepalived-1.2.7
 /usr/share/doc/keepalived-1.2.7/AUTHOR
 /usr/share/doc/keepalived-1.2.7/CONTRIBUTORS
 /usr/share/doc/keepalived-1.2.7/COPYING
 /usr/share/doc/keepalived-1.2.7/ChangeLog
 /usr/share/doc/keepalived-1.2.7/README
 /usr/share/doc/keepalived-1.2.7/TODO
 /usr/share/doc/keepalived-1.2.7/keepalived.conf.HTTP_GET.port
 /usr/share/doc/keepalived-1.2.7/keepalived.conf.IPv6
 /usr/share/doc/keepalived-1.2.7/keepalived.conf.SMTP_CHECK
 /usr/share/doc/keepalived-1.2.7/keepalived.conf.SSL_GET
 /usr/share/doc/keepalived-1.2.7/keepalived.conf.SYNOPSIS
 /usr/share/doc/keepalived-1.2.7/keepalived.conf.fwmark
 /usr/share/doc/keepalived-1.2.7/keepalived.conf.inhibit
 /usr/share/doc/keepalived-1.2.7/keepalived.conf.misc_check
 /usr/share/doc/keepalived-1.2.7/keepalived.conf.misc_check_arg
 /usr/share/doc/keepalived-1.2.7/keepalived.conf.quorum
 /usr/share/doc/keepalived-1.2.7/keepalived.conf.sample
 /usr/share/doc/keepalived-1.2.7/keepalived.conf.status_code
 /usr/share/doc/keepalived-1.2.7/keepalived.conf.track_interface
 /usr/share/doc/keepalived-1.2.7/keepalived.conf.virtual_server_group
 /usr/share/doc/keepalived-1.2.7/keepalived.conf.virtualhost
 /usr/share/doc/keepalived-1.2.7/keepalived.conf.vrrp
 /usr/share/doc/keepalived-1.2.7/keepalived.conf.vrrp.localcheck
 /usr/share/doc/keepalived-1.2.7/keepalived.conf.vrrp.lvs_syncd
 /usr/share/doc/keepalived-1.2.7/keepalived.conf.vrrp.routes
 /usr/share/doc/keepalived-1.2.7/keepalived.conf.vrrp.scripts
 /usr/share/doc/keepalived-1.2.7/keepalived.conf.vrrp.static_ipaddress
 /usr/share/doc/keepalived-1.2.7/keepalived.conf.vrrp.sync
 /usr/share/man/man1/genhash.1.gz
 /usr/share/man/man5/keepalived.conf.5.gz
 /usr/share/man/man8/keepalived.8.gz
 /usr/share/snmp/mibs/KEEPALIVED-MIB.txt
 [root@mysql-a ~]#

KeepAlived双主RealServer A配置
! Configuration File for keepalived

global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
}

vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
#192.168.200.16
#192.168.200.17
#192.168.200.18
192.168.75.110
}
}

vrrp_instance VI_2 {
state BACKUP
interface eth0
virtual_router_id 52
priority 50
advert_int 1
authentication {
auth_type PASS
auth_pass 2222
}
virtual_ipaddress {
#192.168.200.16
#192.168.200.17
#192.168.200.18
192.168.75.111
}
}

KeepAlived双主RealServer B配置
! Configuration File for keepalived

global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
}

vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 50
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
#192.168.200.16
#192.168.200.17
#192.168.200.18
192.168.75.110
}
}

vrrp_instance VI_2 {
state MASTER
interface eth0
virtual_router_id 52
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 2222
}
virtual_ipaddress {
#192.168.200.16
#192.168.200.17
#192.168.200.18
192.168.75.111
}
}
在RealServer B上启动KeepAlived服务
[root@mysql-b keepalived]# chkconfig –list keepalived
keepalived 0:off 1:off 2:off 3:off 4:off 5:off 6:off
[root@mysql-b keepalived]# chkconfig –level 35 keepalived on
[root@mysql-b keepalived]# service keepalived start
Starting keepalived: [ OK ]
[root@mysql-b keepalived]#

查看两个Virtual IP信息
[root@mysql-b keepalived]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UNKNOWN qlen 1000
link/ether 00:0c:29:7c:b3:f2 brd ff:ff:ff:ff:ff:ff
inet 192.168.75.134/24 brd 192.168.75.255 scope global eth0
inet 192.168.75.110/32 scope global eth0
inet 192.168.75.111/32 scope global eth0
inet6 fe80::20c:29ff:fe7c:b3f2/64 scope link
valid_lft forever preferred_lft forever
[root@mysql-b keepalived]#
在RealServer A上启动KeepAlived服务后查看Virtual IP信息
[root@mysql-a keepalived]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UNKNOWN qlen 1000
link/ether 00:0c:29:13:94:f0 brd ff:ff:ff:ff:ff:ff
inet 192.168.75.133/24 brd 192.168.75.255 scope global eth0
inet 192.168.75.110/32 scope global eth0
inet6 fe80::20c:29ff:fe13:94f0/64 scope link
valid_lft forever preferred_lft forever
[root@mysql-a keepalived]#

在RealServer B上再次查看Virtual IP信息
[root@mysql-b keepalived]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:7c:b3:f2 brd ff:ff:ff:ff:ff:ff
inet 192.168.75.134/24 brd 192.168.75.255 scope global eth0
inet 192.168.75.111/32 scope global eth0
inet6 fe80::20c:29ff:fe7c:b3f2/64 scope link
valid_lft forever preferred_lft forever
[root@mysql-b keepalived]#
————————–MySQL

[root@mysql-a ~]# yum install mysql-server mysql

================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
mysql i686 5.1.73-3.el6_5 updates 903 k
mysql-server i686 5.1.73-3.el6_5 updates 8.8 M
Installing for dependencies:
perl-DBD-MySQL i686 4.013-3.el6 base 134 k
perl-DBI i686 1.609-4.el6 base 705 k

Transaction Summary
================================================================================
Install 4 Package(s)
[root@mysql-a ~]# /usr/bin/mysqladmin -u root password ‘mysqlpass’

 

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

server-id=1
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog_format=mixed
expire_logs_days=7
sync_binlog=1

auto_increment_offset=1
auto_increment_increment=2
log_slave_updates

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

—————————
server-id=2
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog_format=mixed
expire_logs_days=7
sync_binlog=1

auto_increment_offset=2
auto_increment_increment=2
log_slave_updates

———————————–
添加复制用户并授权
A

mysql> grant replication slave on *.* to repl@’192.168.75.134′ identified by ‘replpasswd’;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

B

mysql> grant replication slave on *.* to repl@’192.168.75.133′ identified by ‘replpasswd’;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

—————-
锁定表为只读状态,并查看二进制日志的当前位置信息
A

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 | 348 | | mysql |
+——————+———-+————–+——————+
1 row in set (0.00 sec)
B

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000003 | 491 | | mysql |
+——————+———-+————–+——————+
1 row in set (0.00 sec)
——————-
各自指定对方为master主机的连接配置信息和位置信息
A

mysql> change master to master_host=’192.168.75.134′,master_user=’repl’,master_password=’replpasswd’,master_log_file=’mysql-bin.000003′,master_log_pos=491;
Query OK, 0 rows affected (0.03 sec)

mysql>
B

mysql> change master to master_host=’192.168.75.133′,master_user=’repl’,master_password=’replpasswd’,master_log_file=’mysql-bin.000001′,master_log_pos=348;
Query OK, 0 rows affected (0.03 sec)

mysql>
————–
解除表锁定,分别启动Slave并查看复制运行状态
A

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.75.134
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 491
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 491
Relay_Log_Space: 407
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

 

B
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.75.133
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 348
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 348
Relay_Log_Space: 407
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

 

[root@mysql-a ~]# netstat -nt |grep 3306
tcp 0 0 192.168.75.133:3306 192.168.75.134:36244 ESTABLISHED
tcp 0 0 192.168.75.133:60121 192.168.75.134:3306 ESTABLISHED
[root@mysql-a ~]#
[root@mysql-b keepalived]# netstat -nt |grep 3306
tcp 0 0 192.168.75.134:3306 192.168.75.133:60121 ESTABLISHED
tcp 0 0 192.168.75.134:36244 192.168.75.133:3306 ESTABLISHED
[root@mysql-b keepalived]#
———————–
测试:
A
mysql> create database aaa;
Query OK, 1 row affected (0.00 sec)

mysql>
B

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| aaa |
| mysql |
| test |
+——————–+
4 rows in set (0.00 sec)

mysql> create database bbb;
Query OK, 1 row affected (0.00 sec)

mysql>

A

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| aaa |
| bbb |
| mysql |
| test |
+——————–+
5 rows in set (0.01 sec)

mysql>

 

——————–

VIP测试

A

mysql> create database ccc;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on ccc.* to cccuser@’%’;
Query OK, 0 rows affected (0.00 sec)

mysql> set password for cccuser@’%’=password(‘cccpwd’);
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql>
VIP 192.168.75.110

[root@localhost ~]# mysql -u cccuser -pcccpwd -h 192.168.75.110
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> use ccc;
Database changed

mysql> create table list (id int(4) not null primary key auto_increment,
-> name char(20) not null,
-> address char(40) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+—————+
| Tables_in_ccc |
+—————+
| list |
+—————+
1 row in set (0.00 sec)

mysql> quit
Bye
[root@localhost ~]#

VIP 192.168.75.111

[root@localhost ~]# mysql -u cccuser -pcccpwd -h 192.168.75.111
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> use ccc;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+—————+
| Tables_in_ccc |
+—————+
| list |
+—————+
1 row in set (0.00 sec)

mysql> quit
Bye
[root@localhost ~]#
————————-
关闭RealServer B 测试KeepAlived VIP可用性和数据库连接
[root@localhost ~]# mysql -u cccuser -pcccpwd -h 192.168.75.110
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> quit
Bye
[root@localhost ~]# mysql -u cccuser -pcccpwd -h 192.168.75.111
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> quit
Bye
[root@localhost ~]#
在RealServer B关闭情况下的ping测试
[root@localhost ~]# ping -c 4 192.168.75.110
PING 192.168.75.110 (192.168.75.110) 56(84) bytes of data.
64 bytes from 192.168.75.110: icmp_seq=1 ttl=64 time=0.535 ms
64 bytes from 192.168.75.110: icmp_seq=2 ttl=64 time=0.312 ms
64 bytes from 192.168.75.110: icmp_seq=3 ttl=64 time=0.351 ms
64 bytes from 192.168.75.110: icmp_seq=4 ttl=64 time=0.356 ms

— 192.168.75.110 ping statistics —
4 packets transmitted, 4 received, 0% packet loss, time 3001ms
rtt min/avg/max/mdev = 0.312/0.388/0.535/0.088 ms
[root@localhost ~]# ping -c 4 192.168.75.111
PING 192.168.75.111 (192.168.75.111) 56(84) bytes of data.
64 bytes from 192.168.75.111: icmp_seq=1 ttl=64 time=0.572 ms
64 bytes from 192.168.75.111: icmp_seq=2 ttl=64 time=0.301 ms
64 bytes from 192.168.75.111: icmp_seq=3 ttl=64 time=0.452 ms
64 bytes from 192.168.75.111: icmp_seq=4 ttl=64 time=0.293 ms

— 192.168.75.111 ping statistics —
4 packets transmitted, 4 received, 0% packet loss, time 3006ms
rtt min/avg/max/mdev = 0.293/0.404/0.572/0.117 ms
[root@localhost ~]#

7月 252014
 

数据库大小约11GB,执行导出操作

[root@msr11 backup]# time ./db-backup
mysqldump: Couldn’t execute ‘SHOW TRIGGERS LIKE ‘alipay\_recharge”: Out of resources when opening file ‘/tmp/#sql_1de1_0.MYI’ (Errcode: 24) (23)

real 2m41.436s
user 1m41.377s
sys 0m6.794s
[root@msr11 backup]#
查看MySQL的默认文件操作数量限制
mysql> show variables like ‘open%’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| open_files_limit | 1024 |
+——————+——-+
1 row in set (0.00 sec)

mysql>

查看CentOS 6系统默认的文件操作数量限制并修改为最大
[root@msr11 backup]# ulimit -n
1024
[root@msr11 backup]# ulimit -HSn 65535
[root@msr11 backup]# ulimit -n
65535
[root@msr11 backup]#

将修改操作添加为随系统启动生效
[root@msr11 backup]# vi /etc/profile
ulimit -HSn 65535

修改MySQL配置文件调整文件操作数量限制
[root@msr11 backup]# vi /etc/my.cnf
open-files-limit=65535

重新启动数据库后查看当前MySQL的文件操作数量限制
[root@msr11 backup]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@msr11 backup]#
mysql> show variables like ‘open%’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| open_files_limit | 65535 |
+——————+——-+
1 row in set (0.00 sec)

mysql>

再次执行数据库导出操作,无报错
[root@msr11 backup]# time ./db-backup

real 11m38.918s
user 5m48.912s
sys 0m26.113s
[root@msr11 backup]#

2月 242013
 

MySQL 复制(Replication)允许在出现不可避免的故障的情况下提供高可用的数据服务。

MySQL可以实现大量服务器的高效复制。

复制就是复制一个服务器上(称为主节点服务器或者简称主节点)的所有改变到另一个服务器(称为从服务器或者简称从节点)。

复制常见的用途包括:(1)创建一个主节点的备份,以避免主节点崩溃时丢失数据;(2)拥有一份主节点的副本,从而在不干扰其他业务的情况下执行报表和分析工作。

异步复制的好处在于它比同步复制更快,更具可扩展性,但在那些实时数据很重要的情况下,必须采用同步的方式以保证信息总是最新的。

复制的另一个重要应用是通过添加冗余来保证高可用性。

备份的必要性:(1)如果发现错误,一般在它实际发生以后很长时间才发现,这时复制便不再有效。(2)当建立新的服务器时,用于横向扩展的从节点或者备用的新的主节点,都需要对现有服务器做备份并在新的服务器上恢复这个备份映像。

监控的必要性:即使已经正确搭建了复制,还需要理解系统负载,密切监控可能发生的任何问题。

 

内容引用:
(1) O’Reilly- MySQL High Availability

1月 202013
 

#!/usr/bin/perl
print “Content-type: text/htmlnn”;
$cmd=system “/usr/local/mysql/bin/mysqldump –uu201002 -ppasswd d201002 > perldbbackup.mysl”;
if ($cmd)
{
print “Error!!!!”;
}
else
{print “Sucess!!!!”;
}

 

数据库备份

#/usr/local/myal/bin/mysqldump -u Username -pPassword databaeID | gzip > /backup/files/databaseID_`date +%m_%d_%y`.gz

计划任务条目

05 1 * * 1 root /usr/local/mysql/bin/mysqldump -u Username -pPassword databaeID | gzip > /backup/files/databaseID_`date +%m_%d_%y`.gz

12月 042012
 

创建用户

create user username identified by password;

username:用户名
以字母开头,字符数不超过30个,只能包含字母,数字,美元符号$和下划线。
字母区分大小写,默认自动转换为大写,不能使用保留字。
使用小写,非法字符,保留字需加双引号。
password:密码

sql>create user u2013 identified by pass2013;
 create user 成功。

修改用户密码

alter user username identified by pass2014;

Continue reading »