mysql+lvm
两台虚拟机，系统为CentOS 5.4，分别有三块磁盘来做逻辑卷。IP分配及磁盘情况：
HA1 eth0：192.168.0.77 eth1：192.168.10.1 /dev/sdc /dev/sdd /dev/sde
HA2 eth0：192.168.0.69 eth1：192.168.10.2 /dev/sdc /dev/sdd /dev/sde
一、配置逻辑磁盘
查看磁盘情况：
# fdisk -l
Disk /dev/sda: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks IdSystem
/dev/sda1 * 1 13 104391 83Linux
/dev/sda2 14 1305 10377990 8eLinux LVM
Disk /dev/sdb: 6442 MB, 6442450944 bytes
255 heads, 63 sectors/track, 783 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Disk /dev/sdb doesn’t contain a valid partition table
Disk /dev/sdc: 536 MB, 536870912 bytes
64 heads, 32 sectors/track, 512 cylinders
Units = cylinders of 2048 * 512 = 1048576 bytes
Disk /dev/sdc doesn’t contain a valid partition table
Disk /dev/sdd: 536 MB, 536870912 bytes
64 heads, 32 sectors/track, 512 cylinders
Units = cylinders of 2048 * 512 = 1048576 bytes
Disk /dev/sdd doesn’t contain a valid partition table
Disk /dev/sde: 536 MB, 536870912 bytes
64 heads, 32 sectors/track, 512 cylinders
Units = cylinders of 2048 * 512 = 1048576 bytes
Disk /dev/sde doesn’t contain a valid partition table
为磁盘分区：
# fdisk /dev/sdc
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won’t be recoverable.
Warning: invalid flag 0×0000 of partition table 4 will be corrected by w(rite)
Command (m for help): m # 获取帮助
Command action
a toggle a bootable flag
b edit bsd disklabel
c toggle the dos compatibility flag
d delete a partition
l list known partition types
m print this menu
n add a new partition
o create a new empty DOS partition table
p print the partition table
q quit without saving changes
s create a new empty Sun disklabel
t change a partition’s system> u change display/entry units
v verify the partition table
w write table to disk and exit
x extra functionality (experts only)
Command (m for help): n # 新建分区
Command action
e extended
p primary partition (1-4)
p # 新建主分区
Partition number (1-4): 1 # 输入分区号
First cylinder (1-512, default 1): # 回车，默认即可
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-512, default 512): # 回车，使用所有磁盘空间
Using default value 512
Command (m for help): t # 设置分区类型
Selected partition 1
Hex code (type L to list codes): L # 查看分区类型
0Empty 1eHidden W95 FAT1 80Old Minix bfSolaris
1FAT12 24NEC DOS 81Minix / old Lin c1DRDOS/sec (FAT-
2XENIX root 39Plan 9 82Linux swap / So c4DRDOS/sec (FAT-
3XENIX usr 3cPartitionMagic83Linux c6DRDOS/sec (FAT-
4FAT16 Calling ioctl() to re-read partition table.
Syncing disks.
# fdisk /dev/sde
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won’t be recoverable.
Warning: invalid flag 0×0000 of partition table 4 will be corrected by w(rite)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-512, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-512, default 512):
Using default value 512
Command (m for help): t
Selected partition 1
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)
Command (m for help): w
The partition table has been> Calling ioctl() to re-read partition table.
Syncing disks.
分区完成后查看磁盘情况：
# fdisk -l
Disk /dev/sda: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks IdSystem
/dev/sda1 * 1 13 104391 83Linux
/dev/sda2 14 1305 10377990 8eLinux LVM
Disk /dev/sdb: 6442 MB, 6442450944 bytes
255 heads, 63 sectors/track, 783 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Disk /dev/sdb doesn’t contain a valid partition table
Disk /dev/sdc: 536 MB, 536870912 bytes
64 heads, 32 sectors/track, 512 cylinders
Units = cylinders of 2048 * 512 = 1048576 bytes
Device Boot Start End Blocks IdSystem
/dev/sdc1 1 512 524272 8eLinux LVM
Disk /dev/sdd: 536 MB, 536870912 bytes
64 heads, 32 sectors/track, 512 cylinders
Units = cylinders of 2048 * 512 = 1048576 bytes
Device Boot Start End Blocks IdSystem
/dev/sdd1 1 512 524272 8eLinux LVM
Disk /dev/sde: 536 MB, 536870912 bytes
64 heads, 32 sectors/track, 512 cylinders
Units = cylinders of 2048 * 512 = 1048576 bytes
Device Boot Start End Blocks IdSystem
/dev/sde1 1 512 524272 8eLinux LVM
创建物理卷：
# pvcreate /dev/sdc1 /dev/sdd1 /dev/sde1
Physical volume “/dev/sdc1″ successfully created
Physical volume “/dev/sdd1″ successfully created
Physical volume “/dev/sde1″ successfully created
查看物理卷：
# pvdisplay
— Physical volume —
PV Name /dev/sda2
VG Name VolGroup00
PV> Allocatable yes (but full)
PE> Total PE 316
Free PE 0
Allocated PE 316
PV UUID 1zBHox-Dla7-0ozU-0IFp-Onl4-V7V2-R10XXW
“/dev/sdc1″ is a new physical volume of “511.98 MB”
— NEW Physical volume —
PV Name /dev/sdc1
VG Name
PV> Allocatable NO
PE> Total PE 0
Free PE 0
Allocated PE 0
PV UUID DwoEeZ-NmK5-ZDR6-qCmx-vJsw-7Wet-2qGako
“/dev/sdd1″ is a new physical volume of “511.98 MB”
— NEW Physical volume —
PV Name /dev/sdd1
VG Name
PV> Allocatable NO
PE> Total PE 0
Free PE 0
Allocated PE 0
PV UUID YfolqL-6Qlm-bUki-qWTJ-8zIW-zeJI-Ssjxln
“/dev/sde1″ is a new physical volume of “511.98 MB”
— NEW Physical volume —
PV Name /dev/sde1
VG Name
PV> Allocatable NO
PE> Total PE 0
Free PE 0
Allocated PE 0
PV UUID Rhdkyp-MBB6-UeTK-dmuP-6Dza-L69O-sW6eNv
创建逻辑卷组：
# vgcreate dataVg /dev/sdc1 /dev/sdd1 /dev/sde1
Volume group “dataVg” successfully created
创建逻辑卷：
# lvcreate –name dataLv –size 1G dataVg
Logical volume “dataLv” created
查看逻辑卷：
# lvdisplay
— Logical volume —
LV Name /dev/dataVg/dataLv
VG Name dataVg
LV UUID gXPZmP-c41N-Yeu8-mT8U-0sUx-Mu2X-pR1PyE
LV Write Access read/write
LV Status available
# open 0
LV> Current LE 256
Segments 3
Allocation inherit
Read ahead sectors auto
- currently set to 256
Block device 253:2
— Logical volume —
LV Name /dev/VolGroup00/LogVol00
VG Name VolGroup00
LV UUID yTby3S-TYzd-x7fP-T8HJ-GOEg-lt7E-i90qZy
LV Write Access read/write
LV Status available
# open 1
LV> Current LE 284
Segments 1
Allocation inherit
Read ahead sectors auto
- currently set to 256
Block device 253:0
— Logical volume —
LV Name /dev/VolGroup00/LogVol01
VG Name VolGroup00
LV UUID bNfOaD-vcTc-hq4c-7Bd0-3a6S-wD0B-aFZMzM
LV Write Access read/write
LV Status available
# open 1
LV> Current LE 32
Segments 1
Allocation inherit
Read ahead sectors auto
- currently set to 256
Block device 253:1
格式化逻辑卷：
# mkfs.ext3 /dev/dataVg/dataLv
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block>
Fragment> 131072 inodes, 262144 blocks
13107 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=268435456
8 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376
Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done
This filesystem will be automatically checked every 25 mounts or
180 days, whichever comes first.Use tune2fs -c or -i to override.
挂载逻辑卷到/data目录：
# mount /dev/dataVg/dataLv /data/
设置开机自动挂载挂逻辑卷：
# vi /etc/fstab
/dev/dataVg/dataLv /data ext3 defaults 0 0
在HA2上执行上面步骤。
二、安装MySQL并迁移MySQL数据到HA1 /data下。
三、安装MySQL LVM备份工具：
# wget http://search.cpan.org/CPAN/authors/id/S/SH/SHLOMIF/Config-IniFiles-2.54.tar.gz
# tar xzvf Config-IniFiles-2.54.tar.gz
# cd Config-IniFiles-2.54
# perl Makefile.PL
Checking if your kit is complete…
Looks good
Writing Makefile for Config::IniFiles
# make
cp lib/Config/IniFiles.pm blib/lib/Config/IniFiles.pm
Manifying blib/man3/Config::IniFiles.3pm
# make install
Installing /usr/lib/perl5/site_perl/5.8.8/Config/IniFiles.pm
Installing /usr/share/man/man3/Config::IniFiles.3pm
Writing /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/Config/IniFiles/.packlist
Appending installation info to /usr/lib/perl5/5.8.8/i386-linux-thread-multi/perllocal.pod
# cd ..
# wget http://www.lenzg.net/mylvmbackup/mylvmbackup-0.13.tar.gz
# tar xzvf mylvmbackup-0.13.tar.gz
# cd mylvmbackup-0.13
# make install
# cd ..
配置mylvmbackup：
# vi /etc/mylvmbackup.conf
user=root
password=
host=localhost
port=3306
socket=/data/mysql/mysql.sock
mycnf=/etc/my.cnf
#
# LVM-specific options
#
vgname=dataVg
lvname=dataLv
backuplv=backupLv
lvsize=0.45G
#
# File system specific options
#
xfs=0
mountdir=/var/tmp/mylvmbackup/mnt/
backupdir=/var/tmp/mylvmbackup/backup/
relpath=
注意修改上面标红的配置项。
创建下面目录：
# mkdir -p/var/tmp/mylvmbackup/backup
# mkdir -p/var/tmp/mylvmbackup/mnt
查看数据库情况（employees库使用InnoDB 引擎）：
# mysql
Welcome to the MySQL monitor.Commands end with ; or g.
Your MySQL connection> Server version: 5.0.77 Source distribution
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| employees |
| mysql |
+——————–+
3 rows in set (0.01 sec)
mysql> use employees;
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 engines;
+————+———+—————————————————————-+
| Engine | Support | Comment |
+————+———+—————————————————————-+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys |
| BerkeleyDB | YES | Supports transactions and page-level locking |
| BLACKHOLE| NO | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE | NO | Example storage engine |
| ARCHIVE | NO | Archive storage engine |
| CSV | NO | CSV storage engine |
| ndbcluster | NO | Clustered, fault-tolerant, memory-based tables |
| FEDERATED| NO | Federated MySQL storage engine |
| MRG_MYISAM | YES | Collection of> | ISAM | NO | Obsolete storage engine |
+————+———+—————————————————————-+
12 rows in set (0.00 sec)
mysql> show tables;
+———————+
| Tables_in_employees |
+———————+
| departments |
| dept_emp |
| dept_manager |
| employees |
| salaries |
|> +———————+
6 rows in set (0.00 sec)
mysql> select count(*) from employees;
+———-+
| count(*) |
+———-+
| 300024 |
+———-+
1 row in set (1.94 sec)
mysql> quit
Bye
lvm快照备份数据库：
# mylvmbackup
20091125 14:50:10 Info: Connecting to database…
20091125 14:50:10 Info: Flushing tables with read lock… # 锁定库表，准备备份
20091125 14:50:10 Info: Taking position record into /tmp/mylvmbackup-backup-20091125_145009_mysql-odzMgs.pos… # 如开启bin-log则记录日志位置信息
20091125 14:50:10 Info: Running: lvcreate -s –size=0.45G –name=backupLv /dev/dataVg/dataLv
File descriptor 4 (socket:) leaked on lvcreate invocation. Parent PID 6062: /usr/bin/perl
Rounding up> Logical volume “backupLv” created
20091125 14:50:13 Info: DONE: taking LVM snapshot # 只需3s完成lvm快照备份
20091125 14:50:13 Info: Unlocking tables… # 完成备份，解除锁定，至此数据库完全恢复正常访问
20091125 14:50:13 Info: Disconnecting from database…
20091125 14:50:13 Info: Mounting snapshot…
20091125 14:50:13 Info: Running: mount -o rw /dev/dataVg/backupLv /var/tmp/mylvmbackup/mnt/backup
20091125 14:50:13 Info: DONE: mount snapshot
20091125 14:50:13 Info: Copying/tmp/mylvmbackup-backup-20091125_145009_mysql-odzMgs.pos to/var/tmp/mylvmbackup/mnt/backup-pos/backup-20091125_145009_mysql.pos…
20091125 14:50:13 Info: Copying /etc/my.cnf to /var/tmp/mylvmbackup/mnt/backup-pos/backup-20091125_145009_mysql_my.cnf…
20091125 14:50:13 Info: Taking actual backup…
20091125 14:50:13 Info: Creating tar archive /var/tmp/mylvmbackup/backup/backup-20091125_145009_mysql.tar.gz
20091125 14:50:13 Info: Running: cd ‘/var/tmp/mylvmbackup/mnt’ ;’tar’cvf – backup/backup-pos/backup-20091125_145009_mysql.posbackup-pos/backup-20091125_145009_mysql_my.cnf| gzip –stdout –verbose–best ->/var/tmp/mylvmbackup/backup/backup-20091125_145009_mysql.tar.gz.INCOMPLETE-54lIVbU
backup/
backup/lost+found/
backup/logs/
backup/logs/www.access.log
backup/logs/error.log
backup/backup/
backup/backup/cib.xml
backup/backup/ifcfg-lo:0
backup/mysql/
backup/mysql/ib_logfile0
tar: backup/mysql/mysql.sock: socket ignored
backup/mysql/employees/
backup/mysql/employees/departments.frm
backup/mysql/employees/dept_emp.frm
backup/mysql/employees/salaries.frm
backup/mysql/employees/employees.frm
backup/mysql/employees/db.opt
backup/mysql/employees/dept_manager.frm
backup/mysql/employees/titles.frm
backup/mysql/ib_logfile1
backup/mysql/mysql/
backup/mysql/mysql/help_category.MYD
backup/mysql/mysql/help_topic.MYI
backup/mysql/mysql/help_relation.MYD
backup/mysql/mysql/db.frm
backup/mysql/mysql/time_zone.frm
backup/mysql/mysql/time_zone.MYD
backup/mysql/mysql/time_zone_transition.MYI
backup/mysql/mysql/columns_priv.MYI
backup/mysql/mysql/tables_priv.frm
backup/mysql/mysql/host.MYD
backup/mysql/mysql/procs_priv.MYI
backup/mysql/mysql/proc.frm
backup/mysql/mysql/user.MYD
backup/mysql/mysql/db.MYI
backup/mysql/mysql/time_zone_name.MYI
backup/mysql/mysql/time_zone.MYI
backup/mysql/mysql/func.MYI
backup/mysql/mysql/help_keyword.MYI
backup/mysql/mysql/help_topic.MYD
backup/mysql/mysql/procs_priv.MYD
backup/mysql/mysql/db.MYD
backup/mysql/mysql/time_zone_name.MYD
backup/mysql/mysql/host.MYI
backup/mysql/mysql/time_zone_leap_second.frm
backup/mysql/mysql/time_zone_transition_type.MYD
backup/mysql/mysql/time_zone_transition_type.MYI
backup/mysql/mysql/help_relation.MYI
backup/mysql/mysql/time_zone_leap_second.MYI
backup/mysql/mysql/help_keyword.MYD
backup/mysql/mysql/user.frm
backup/mysql/mysql/func.MYD
backup/mysql/mysql/tables_priv.MYI
backup/mysql/mysql/tables_priv.MYD
backup/mysql/mysql/time_zone_transition.frm
backup/mysql/mysql/user.MYI
backup/mysql/mysql/help_category.frm
backup/mysql/mysql/procs_priv.frm
backup/mysql/mysql/columns_priv.MYD
backup/mysql/mysql/help_category.MYI
backup/mysql/mysql/help_keyword.frm
backup/mysql/mysql/time_zone_leap_second.MYD
backup/mysql/mysql/proc.MYI
backup/mysql/mysql/proc.MYD
backup/mysql/mysql/time_zone_transition_type.frm
backup/mysql/mysql/time_zone_transition.MYD
backup/mysql/mysql/func.frm
backup/mysql/mysql/time_zone_name.frm
backup/mysql/mysql/host.frm
backup/mysql/mysql/help_relation.frm
backup/mysql/mysql/help_topic.frm
backup/mysql/mysql/columns_priv.frm
backup/mysql/ibdata1
backup/html/
backup/html/www.baihe.com/
backup/html/www.baihe.com/test.html
backup/html/www.baihe.com/index.html
backup-pos/backup-20091125_145009_mysql.pos
backup-pos/backup-20091125_145009_mysql_my.cnf
64.0%
20091125 14:56:00 Info: DONE: create tar archive
20091125 14:56:01 Info: Cleaning up…
20091125 14:56:01 Info: Running: umount /var/tmp/mylvmbackup/mnt/backup
20091125 14:56:02 Info: DONE: Unmounting /var/tmp/mylvmbackup/mnt/backup
20091125 14:56:02 Info: LVM Usage stats:
20091125 14:56:02 Info: LV VG Attr LSize Origin Snap%Move Log Copy%Convert
20091125 14:56:02 Info: backupLv dataVg swi-a- 464.00M dataLv 0.09
20091125 14:56:02 Info: Running: lvremove -f /dev/dataVg/backupLv
Logical volume “backupLv” successfully removed
20091125 14:56:03 Info: DONE: Removing snapshot
# cd /var/tmp/mylvmbackup/backup
You have new mail in /var/spool/mail/root
# ls
backup-20091125_145009_mysql.tar.gz
# scp backup-20091125_145009_mysql.tar.gz HA2:/root/
root@ha2’s password:
backup-20091125_145009_mysql.tar.gz 100% 80MB 799.2KB/s 01:42
在HA2上进行有效性验证：
# tar xzvf /root/backup-20091125_145009_mysql.tar.gz
# ls
backupbackup-poslost+found
You have new mail in /var/spool/mail/root
# cd backup
# ls
backuphtmllogslost+foundmysql
# mv mysql/ ..
# cd ..
# service mysqld start
Starting MySQL:
# mysql
Welcome to the MySQL monitor.Commands end with ; or g.
Your MySQL connection> Server version: 5.0.77 Source distribution
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| employees |
| mysql |
+——————–+
3 rows in set (0.00 sec)
mysql> use employees;
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_employees |
+———————+
| departments |
| dept_emp |
| dept_manager |
| employees |
| salaries |
|> +———————+
6 rows in set (0.00 sec)
mysql> select count(*) from employees;
+———-+
| count(*) |
+———-+
| 300024 |
+———-+
1 row in set (0.58 sec)
mysql>
四、配置数据库主从复制：
HA1（主）
配置mysql
# cat /etc/my.cnf
datadir=/data/mysql
socket=/data/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
log-bin=/data/mysql/log/mysql-bin.log
server-id=1
log-error=/data/mysql/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
socket=/data/mysql/mysql.sock
如果对配置文件有改动需要重启MySQL。
lvm快照备份数据库：
# mylvmbackup
拷贝备份文件到HA2
# scp backup-20091125_155132_mysql.tar.gz HA2:/root/
root@ha2’s password:
backup-20091125_155132_mysql.tar.gz 100% 80MB 1.2MB/s 01:07
在主库上添加同步账户：
# mysql
Welcome to the MySQL monitor.Commands end with ; or g.
Your MySQL connection> Server version: 5.0.77-log Source distribution
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘rep’@'192.168.10.%’> HA2（从）
解压主库备份数据文件到从库数据目录：
# tar xzvf /root/backup-20091125_155132_mysql.tar.gz
拷贝数据文件到mysql数据目录：
# mv backup/mysql/ .
查看备份时mysql日志位置：
# cat backup-pos/backup-20091125_155132_mysql.pos
Master:File=mysql-bin.000001
Master:Position=244
Master:Binlog_Do_DB=
Master:Binlog_Ignore_DB=
修改mysql配置文件：
# cat /etc/my.cnf
datadir=/data/mysql
socket=/data/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
server-id=2
log-error=/data/mysql/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
socket=/data/mysql/mysql.sock
启动MySQL：
# service mysqld start
Starting MySQL:
配置mysql从库：
# mysql
Welcome to the MySQL monitor.Commands end with ; or g.
Your MySQL connection> Server version: 5.0.77 Source distribution
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.10.1′,
-> MASTER_USER=’rep’,
-> MASTER_PASSWORD=’slavepass’,
-> MASTER_LOG_FILE=’mysql-bin.000001′,
-> MASTER_LOG_POS=244;
Query OK, 0 rows affected (0.00 sec)
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.1
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 500
Relay_Log_File: mysqld-relay-bin.000003
Relay_Log_Pos: 637
Relay_Master_Log_File: mysql-bin.000002
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: 500
Relay_Log_Space: 637
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
1 row in set (0.00 sec)
mysql> quit
Bye
验证同步配置情况：
在HA1上插入一条数据：
# mysql
Welcome to the MySQL monitor.Commands end with ; or g.
Your MySQL connection> Server version: 5.0.77-log Source distribution
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.
mysql> use employees;
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> insert into employees values (66666666,’1982-10-17′,’Shi’,'Dongliang’,”M”,’2008-06-01′) ;
Query OK, 1 row affected (0.03 sec)
在HA2上查询，看同步情况：
# mysql
Welcome to the MySQL monitor.Commands end with ; or g.
Your MySQL connection> Server version: 5.0.77 Source distribution
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.
mysql> use employees;
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> select * from employees where emp_no=66666666;
+———-+————+————+———–+——–+————+
| emp_no | birth_date | first_name | last_name | gender | hire_date|
+———-+————+————+———–+——–+————+
| 66666666 | 1982-10-17 | Shi | Dongliang| M | 2008-06-01 |
+———-+————+————+———–+——–+————+
1 row in set (0.04 sec)
不管你使用MyISAM存储引擎还是InnoDB存储引擎，通过LVM快照都很容易得到一个一致的MySQL备份。LVM快照备份MySQL数据时，一但锁定数据库完成只需几秒就可以做一个快照备份，释放表锁，数据库便可完全恢复正常访问，剩下的事情便是压缩数据备份并拷贝到从库进行恢复（根据数据大小，这可能需要很长时间，但基本不会再影响主库了）。
页:
[1]