MySQL+Heartbeat+DRBD架构部署(9)

开启主数据的binlog参数，和设置server-id
egrep "server-id|log-bin" /etc/my.cnf
 sed -i 's%#log-bin=mysql-bin%log-bin=mysql-bin%g' /etc/my.cnf
egrep "server-id|log-bin" /etc/my.cnf
=======================================
[root@MYSQL_S ~]# egrep "server-id|log-bin" /etc/my.cnf
server-id      = 1
#log-bin=mysql-bin
[root@MYSQL_S ~]#  sed -i 's%#log-bin=mysql-bin%log-bin=mysql-bin%g' /etc/my.cnf
[root@MYSQL_S ~]# egrep "server-id|log-bin" /etc/my.cnf                       
server-id      = 1
log-bin=mysql-bin
[root@MYSQL_S ~]#
=======================================

重启数据库
/etc/init.d/mysqld restart
netstat -lntup|grep 3306
########################################
[root@MYSQL_S ~]# /etc/init.d/mysqld restart
Shutting down MySQL..... SUCCESS!
Starting MySQL. SUCCESS!
[root@MYSQL_S ~]# netstat -lntup|grep 3306
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                  LISTEN      904/mysqld         
[root@MYSQL_S ~]#
########################################

查看是否记录bin_log日志
[root@MYSQL_S ~]# ll /data/
total 20524
-rw-rw---- 1 mysql mysql 10485760 Nov  5 10:48 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Nov  5 10:48 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Nov  4 23:55 ib_logfile1
drwx------ 2 mysql mysql    4096 Nov  4 23:55 mysql
-rw-rw---- 1 mysql mysql      106 Nov  5 10:48 mysql-bin.000001        ##有这个，证明在记录主从同步
-rw-rw---- 1 mysql mysql      19 Nov  5 10:48 mysql-bin.index
-rw-r----- 1 mysql mysql    8427 Nov  5 10:29 MYSQL_M.err
-rw-r----- 1 mysql mysql    9010 Nov  5 10:48 MYSQL_S.err
-rw-rw---- 1 mysql mysql        4 Nov  5 10:48 MYSQL_S.pid
drwx------ 2 mysql mysql    4096 Nov  4 23:55 test
[root@MYSQL_S ~]#
########################################

##在主数据中创建测试用的数据库和表。
mysql
create database lvnian;
use lvnian;
create table test(
id int(4) not null auto_increment,
name char(20) not null,
primary key(id)
);
flush privileges;
quit;
mysql -uroot -e "use lvnian; select * from test;"
for n in `seq 100`;do mysql -uroot -e "use lvnian;insert test values($n,'lvnian$n'); " ;done;
mysql -uroot -e "use lvnian; select * from test;"
################################################

查看是否开启binlog
[root@MYSQL_S ~]#  mysql -uroot  -e "show variables like 'log_bin'"                   
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin      | ON    |
+---------------+-------+
[root@MYSQL_S ~]#

#######

创建用于同步数据的账号rep
mysql> grant replication slave on *.* to 'rep'@'10.0.0.%' identified by 'lvnian123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
=========
或者用下面命令：
mysql -uroot -e "grant replication slave on *.* to 'rep'@'10.0.0.%' identified by 'lvnian123456'; flush privileges;"
mysql -uroot -e "select user,host from mysql.user;" |grep rep
------
[root@MYSQL_S ~]# mysql -uroot -e "select user,host from mysql.user;" |grep rep
rep    10.0.0.%
[root@MYSQL_S ~]#

锁表
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
或者用下面命令：
mysql -uroot -e "flush tables with read lock;"

查看当前日志名称和位置
mysql -uroot -e " show master status;" 
mysql -uroot -e "show master logs;"
########################################
[root@MYSQL_S ~]# mysql -uroot -e " show master status;" 
+------------------+----------+--------------+------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |    10447 |              |                  |
+------------------+----------+--------------+------------------+
[root@MYSQL_S ~]# mysql -uroot -e "show master logs;"
+------------------+-----------+
| Log_name        | File_size |
+------------------+-----------+
| mysql-bin.000001 |    10447 |
+------------------+-----------+
[root@MYSQL_S ~]#
######################################

备份当前主数据库的数据

另开一个窗口