###mysql查看binlog日志内容

https://blog.csdn.net/nuli888/article/details/52106910

mysql的binlog日志位置可通过show variables like ‘%datadir%’;查看，直接打开无法查看，要看其内容2个办法：

1、登录到mysql查看binlog
只查看第一个binlog文件的内容
mysql> show binlog events;

查看指定binlog文件的内容
mysql> show binlog events in ‘mysql-bin.000002’;

mysql> show binlog events in ‘mysql-bin.000001’;
+——————+——+————-+———–+————-+———————————————————–+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+——————+——+————-+———–+————-+———————————————————–+
| mysql-bin.000001 | 4 | Format_desc | 195 | 106 | Server ver: 5.1.73-log, Binlog ver: 4 |
| mysql-bin.000001 | 106 | Query | 195 | 198 | use `hadoop`; delete from user where id=3 |
| mysql-bin.000001 | 198 | Intvar | 195 | 226 | INSERT_ID=4 |
| mysql-bin.000001 | 226 | Query | 195 | 332 | use `hadoop`; INSERT INTO user (id,name)VALUES (NULL,1) |
| mysql-bin.000001 | 332 | Query | 195 | 424 | use `hadoop`; delete from user where id=3 |
| mysql-bin.000001 | 424 | Intvar | 195 | 452 | INSERT_ID=5 |
| mysql-bin.000001 | 452 | Query | 195 | 560 | use `hadoop`; INSERT INTO user (id,name)VALUES (NULL,222) |
| mysql-bin.000001 | 560 | Query | 195 | 660 | use `hadoop`; DELETE FROM `user` WHERE (`id`=’1′) |
| mysql-bin.000001 | 660 | Intvar | 195 | 688 | INSERT_ID=6 |
| mysql-bin.000001 | 688 | Query | 195 | 795 | use `hadoop`; INSERT INTO `user` (`name`) VALUES (‘555’) |
| mysql-bin.000001 | 795 | Intvar | 195 | 823 | INSERT_ID=7 |
| mysql-bin.000001 | 823 | Query | 195 | 930 | use `hadoop`; INSERT INTO `user` (`name`) VALUES (‘555’) |
| mysql-bin.000001 | 930 | Intvar | 195 | 958 | INSERT_ID=8 |
| mysql-bin.000001 | 958 | Query | 195 | 1065 | use `hadoop`; INSERT INTO `user` (`name`) VALUES (‘555’) |
| mysql-bin.000001 | 1065 | Intvar | 195 | 1093 | INSERT_ID=9 |
| mysql-bin.000001 | 1093 | Query | 195 | 1200 | use `hadoop`; INSERT INTO `user` (`name`) VALUES (‘555’) |
| mysql-bin.000001 | 1200 | Query | 195 | 1300 | use `hadoop`; DELETE FROM `user` WHERE (`id`=’9′) |
| mysql-bin.000001 | 1300 | Query | 195 | 1400 | use `hadoop`; DELETE FROM `user` WHERE (`id`=’8′) |
| mysql-bin.000001 | 1400 | Query | 195 | 1500 | use `hadoop`; DELETE FROM `user` WHERE (`id`=’7′) |
| mysql-bin.000001 | 1500 | Query | 195 | 1600 | use `hadoop`; DELETE FROM `user` WHERE (`id`=’4′) |
| mysql-bin.000001 | 1600 | Query | 195 | 1700 | use `hadoop`; DELETE FROM `user` WHERE (`id`=’5′) |
| mysql-bin.000001 | 1700 | Query | 195 | 1800 | use `hadoop`; DELETE FROM `user` WHERE (`id`=’6′) |
| mysql-bin.000001 | 1800 | Intvar | 195 | 1828 | INSERT_ID=10 |
| mysql-bin.000001 | 1828 | Query | 195 | 1935 | use `hadoop`; INSERT INTO `user` (`name`) VALUES (‘555’) |
| mysql-bin.000001 | 1935 | Intvar | 195 | 1963 | INSERT_ID=11 |
| mysql-bin.000001 | 1963 | Query | 195 | 2070 | use `hadoop`; INSERT INTO `user` (`name`) VALUES (‘666’) |
| mysql-bin.000001 | 2070 | Intvar | 195 | 2098 | INSERT_ID=12 |
| mysql-bin.000001 | 2098 | Query | 195 | 2205 | use `hadoop`; INSERT INTO `user` (`name`) VALUES (‘777’) |
+——————+——+————-+———–+————-+———————————————————–+

查看当前正在写入的binlog文件
mysql> show master status\G

mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 106
Binlog_Do_DB:
Binlog_Ignore_DB: mysql,information_schema,performance_schema
1 row in set (0.00 sec)

获取binlog文件列表
mysql> show binary logs;

mysql> show binary logs;
+——————+———–+
| Log_name | File_size |
+——————+———–+
| mysql-bin.000001 | 3548 |
| mysql-bin.000002 | 106 |
+——————+———–+
2 rows in set (0.00 sec)

2、用mysqlbinlog工具查看
基于开始/结束时间
[root@hd3 ~]# mysqlbinlog –start-datetime=’2016-08-02 00:00:00′ –stop-datetime=’2016-08-03 23:01:01′ -d hadoop /var/lib/mysql/mysql-bin.000001

基于pos值，注：hadoop是库名，/var/lib/mysql/mysql-bin.000001是二进制文件路径
[root@hd3 ~]# mysqlbinlog –start-position=2098 –stop-position=2205 -d hadoop /var/lib/mysql/mysql-bin.000001

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160803 17:49:51 server id 195 end_log_pos 106 Start: binlog v 4, server v 5.1.73-log created 160803 17:49:51 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG ‘
P76hVw/DAAAAZgAAAGoAAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAA/vqFXEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
‘/*!*/;
# at 2098
#160803 18:53:56 server id 195 end_log_pos 2205 Query thread_id=1481 exec_time=115 error_code=0
use `hadoop`/*!*/;
SET TIMESTAMP=1470221636/*!*/;
SET @@session.pseudo_thread_id=1481/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
INSERT INTO `user` (`name`) VALUES (‘777’)
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

#############12

查看是否开启
mysql> show variables like ‘log_bin%’;

2、5.7及以后的版本
在5.6的版本情况下需要多添加一个参数
server-id=123456 #123456是唯一的值就好

###通过 server-id 来区分是否主库还是备库
server-id只需采用ip地址的整数形式如:
select INET_ATON(‘10.10.227.198’)，server_id=180806598

#############sample 0 使用mysqlbackup + 主从复制实施主从搭建 (for 5.6)

–mysqlbackup在187的/下

–准备工具mysqlbackup
cd /dbsoft/lxy/mysql/nbu
scp * root@10.10.220.156:/tmp/dba
scp * root@10.10.227.196:/tmp/dba

cp mysqlbackup /usr/bin/mysqlbackup

su – mysql
mysqlbackup

#–准备login-path
#find / -name mysql_config_editor
#/db/mysql/app/mysql/bin/mysql_config_editor

#–edit user mysql bash_profile
#PATH=$PATH:$HOME/bin:/db/mysql/app/mysql/bin

#–check
#mysql_config_editor print –login-path=root

##准备mysql 编译版版本介质，并安装
scp mysql-5.6.25.tar.gz root@10.10.227.196:/tmp/dba
cd $mysql_home
mkdir data app
cp mysql-5.6.25.tar.gz $mysql_home
gunzip *.gz
tar -xvf *.tar
mv mysql-5.6.25 mysql

–感谢老刘先生
–https://blog.csdn.net/weiganliu/article/details/49126421
–DCMAKE_INSTALL_PREFIX= 指向mysql安装目录
-DMYSQL_DATADIR=/var/lib/mysql 指向mysql数据文件目录
–DSYSCONFDIR=/etc/mysql 指向mysql配置文件目录（/etc/mysql）【默认/etc，可以不指派】
–若想启用某个引擎的支持：-DWITH_<ENGINE>_STORAGE_ENGINE=1
–如：
—DWITH_INNOBASE_STORAGE_ENGINE=1
—DDEFAULT_COLLATION=utf8_general_ci 设定默认排序规则（utf8_general_ci快速/utf8_unicode_ci准确）
–DMYSQL_UNIX_ADDR= /db/mysql/data/mysqltmp/mysql.sock

cmake -DCMAKE_INSTALL_PREFIX=/db/db1/mysql/app -DMYSQL_DATADIR=/db/db1/mysql/data -DSYSCONFDIR=/etc -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_UNIX_ADDR=/db/db1/mysql/data/mysqltmp/mysql.sock -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci

make && make install

chown mysql:mysql /db/db1/mysql/app/mysql

ls /usr/bin/mysql
cp /db/db1/mysql/app/bin/mysql /usr/bin/mysql

1、在MySQL的生产从库进行mysqlbackup的全备

##mysqlbackup –login-path=root –socket=/user/my$port/var/mysql.sock –backup-dir=$mybackdir_full ##–backup-image=$mybackdir_full/mybackup.mbi –compress backup-to-image

mysqlbackup -uroot -p –socket=/db/mysql/data/mysqltmp/mysql.sock –backup-dir=/db/mysql/bak \
–backup-image=/db/mysql/bak/mybackup.mbi –compress backup-to-image

2、将备份文件拷贝到目标主机
cd /db/mysql/bak
scp -rp * root@10.10.227.196:/db/db1/mysql/bak

3、在目标库进行恢复

##mysqlbackup –defaults-file=$mybackdir_full/backup-my.cnf –datadir=/user/my$port/data ##–backup_image=$mybackdir_full/mybackup.mbi –backup_dir=$mybackdir_full copy-back-and-apply-log –uncompress

export mybackdir_full=/db/db1/mysql/bak

##这个命令貌似换行有问题，只能用notepad 放在一行执行，
mysqlbackup –defaults-file=$mybackdir_full/server-my.cnf –datadir=/db/db1/mysql/data/mydata \ –backup_image=$mybackdir_full/mybackup.mbi –backup_dir=$mybackdir_full copy-back-and-apply-log \ –uncompress

4、启动数据库

–cd /user/mysql/base
–nohup ./bin/mysqld_safe –defaults-file=/user/my${port}/my.cnf &

cp /db/db1/mysql/bak/server-my.cnf /db/db1/mysql/app/my.cnf

–检查my.cnf
[mysqld]
basedir = /db/db1/mysql/app
datadir = /db/db1/mysql/data/mydata
log-error = /db/db1/mysql/app/mysql_error.log
pid-file = /db/db1/mysql/app/mysql.pid
#user = mysql
#tmpdir = /tmp
tmpdir=/db/db1/mysql/data/mydata/tmp
socket=/db/db1/mysql/data/mysqltmp/mysql.sock
slow_query_log_file=/db/db1/mysql/data/mydata/mysql-slow.log
slave_load_tmpdir=/db/db1/mysql/data/mydata/tmp
plugin_dir=/db/db1/mysql/app/lib/plugin/
slave_load_tmpdir=/db/db1/mysql/data/mydata/tmp
bind_address=10.10.227.198
character_sets_dir=/db/db1/mysql/app/share/charsets/
general_log_file=/db/db1/mysql/data/mydata/pdb1db02.log
lc_messages_dir=/db/db1/mysql/app/share/
log_bin=/db/db1/mysql/data/mydata/mysql-bin
log_bin_index=/db/db1/mysql/data/mydata/mysql-bin.index
log_error=/db/db1/mysql/data/mydata/mysql-error.log

cd /db/db1/mysql/app/
nohup ./bin/mysqld_safe –defaults-file=/db/db1/mysql/app/my.cnf &

##感谢散尽浮华
##测试GTID 是否要开了
“show variables like ‘%gtid%’;”查看
/db/mysql/app/mysql/my.cnf
gtid_mode=on
log-bin=mysql-bin
log-slave-updates=1
enforce-gtid-consistency=1

show master status;

##调整my.cnf文件中的server_id参数,这条命令可以在主库执行，但是IP 用的是从库VIP
###通过 server-id 来区分是否主库还是备库
server-id只需采用ip地址的整数形式如:
select INET_ATON(‘10.10.227.198’)，server_id=180806598

show variables like ‘%server_id%’;

5、为了接续复制需要重新设置gtid_purged，

运行$mybackdir_full/meta/backup_gtid_executed.sql重置gtid_purged

–@/db/db1/mysql/bak/meta/backup_gtid_executed.sql

SET @@GLOBAL.GTID_PURGED=’52714855-1870-11e8-bce2-005056bd438d:1-3′;

6、slave 操作，修改MASTER，MASTER_HOST需要设置为生产从库IP

GRANT REPLICATION SLAVE ON *.* TO ‘resync’@’%’ IDENTIFIED BY ‘Resync$123’;
flush flush privileges;

show grants for ‘resync’@’%’;

CHANGE MASTER TO MASTER_HOST=’10.10.220.157′, MASTER_USER=’resync’,Master_Port=3306, MASTER_PASSWORD=’Resync$123′, MASTER_AUTO_POSITION=1;

7、RESET SLAVE

必须reset，否则会报错：ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

8、START SLAVE

9、SHOW SLAVE STATUS\G
如下三项值说明同步正常
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

从库设置为read-only
show variables like ‘read_only’;
调整前状态
+—————+——-+
| Variable_name | Value |
+—————+——-+
| read_only | OFF |
+—————+——-+

调整为只读
set global read_only=on;

10.mysql> show master status;检查主库状态

11. 主库/备库建表验证
use tmp
create table tb1 ( id int);
create table tb2 ( id int, age int, name char(20), primary key(id) );

12. 数据库名字验证
show databases;

###for mysql 5.7

##感谢关键步就几步
https://www.cnblogs.com/javabg/p/9951852.html

strings /lib64/libc.so.6 | grep GLIBC

cp /dbsoft/lxy/mysql/nbu/mysqlbackup /usr/bin/mysqlbackup

scp mysql-advanced-5.7.24-linux-glibc2.12-x86_64.tar.gz cradmin@56.18.99.206:/tmp/dba
tar -xzvf mysql-advanced-5.7.24-linux-glibc2.12-x86_64.tar.gz

groupadd mysql
useradd -r -g mysql mysql
mkdir /crabank/mysql

mysql主目录处理
在software目录下移动文件到/usr/local/mysql:

mv mysql-advanced-5.7.24-linux-glibc2.12-x86_64 /crbank/mysql
cd /crbank/mysql
mv mysql-advanced-5.7.24-linux-glibc2.12-x86_64 base

chown -R mysql:mysql /crbank/mysql
##cp /db/db1/mysql/app/bin/mysql /usr/bin/mysql
cd /crbank/mysql
mkdir data

show variables like ‘%gtid%’;

edit /crbank/mysql/data/3306/my.cnf
gtid_mode=on
log-bin=mysql-bin
log-slave-updates=1
enforce-gtid-consistency=1

三、重启
su – mysql
mysqladmin -uusername -p shutdown

/crbank/mysql/base/bin/mysqld_safe –defaults-file=/crbank/mysql/data/3306/my.cnf &
##mysqld_safe –defaults-file=/**/**/my.cnf &

mysqlbackup -uroot -p –socket=/crbank/mysql/data/3306/mysqltmp/mysql.sock –backup-dir=/crbank/mysql/bak \
–backup-image=/crbank/mysql/bak/mybackup.mbi –compress backup-to-image

select INET_ATON(‘10.10.227.198’)，
server_id=180806598

############sample 主从复制准备 5.6 版本之前老的方式

配置主数据库：
my.cnf：
server-id       = 1
log-bin 
重启数据库
登录并查看：
[root@Mysql-server ~]# mysql -uroot -p199429
mysql> show variables like ‘log_bin‘;  
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
mysql> show variables like ‘server_id‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
建立主从复制账号：
mysql> grant replication slave on *.* to ‘rep‘@‘192.1.1.%‘ identified by ‘199429‘;
mysql> select user,host from mysql.user;
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| root      | 127.0.0.1 |
| bbs       | 192.1.1.% |
| keer      | 192.1.1.% |
| rep       | 192.1.1.% |
| wordpress | 192.1.1.% |
| root      | localhost |
+-----------+-----------+
实现对主数据库锁表只读：
mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)mysql> show variables like ‘%timeout%‘;
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 28800    |#####
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 28800    |#####
+----------------------------+----------+
10 rows in set (0.00 sec)
查看主库状态：
mysql> show master status;
+-------------------------+----------+--------------+------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------------+----------+--------------+------------------+
| Mysql-server-bin.000001 |      962 |              |                  |
+-------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
新开窗口备份导出数据：
[root@Mysql-server ~]# mkdir -p /server/backup/ 
[root@Mysql-server ~]# mysqldump -uroot -p199429 --events -A -B |gzip >/server/backup/mysql_bak.$(date +%F).sql.gz
在此查看主库状态是否变化：
mysql> show master status;
+-------------------------+----------+--------------+------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------------+----------+--------------+------------------+
| Mysql-server-bin.000001 |      962 |              |                  |
+-------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)从数据库：
配置文件my.cnf
server-id       = 2  ####保证唯一性
重启从数据库
登录从数据库：
[root@Mysql-server_02 backup]# mysql -uroot -p199429 -S /data/3306/mysql.sock 
mysql> show variables like ‘log_bin‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like ‘server_id‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 3     |
+---------------+-------+
恢复MySQLdump的数据
[root@Mysql-server_02 backup]# cd /server/backup/
[root@Mysql-server_02 backup]# gzip -d mysql_bak.2017-03-23.sql.gz
[root@Mysql-server_02 backup]# mysql -uroot -p‘199429‘ -S /data/3306/mysql.sock <mysql_bak.2017-03-23.sql 登录数据库配置复制参数
[root@Mysql-server_02 backup]# mysql -uroot -p‘199429‘ -S /data/3306/mysql.sock
mysql> CHANGE MASTER TO
    -> MASTER_HOST=‘192.1.1.11‘,
    -> MASTER_PORT=3306,
    -> MASTER_USER=‘rep‘,
    -> MASTER_PASSWORD=‘199429‘,
    -> MASTER_LOG_FILE=‘Mysql-server-bin.000001‘,
    -> MASTER_LOG_POS=962;
费登录状态执行方法：
[root@Mysql-server_02 backup]# mysql -uroot -p‘199429‘ -S /data/3306/mysql.sock<< EOF
CHANGE MASTER TO
    MASTER_HOST=‘192.1.1.11‘,
    MASTER_PORT=3306,
    MASTER_USER=‘rep‘,
    MASTER_PASSWORD=‘199429‘,
    MASTER_LOG_FILE=‘Mysql-server-bin.000001‘,
    MASTER_LOG_POS=962;
EOF
实际修改从库中的master.info文件
[root@Mysql-server_02 backup]# cat /data/3306/data/master.info 
18
mysql-server-bin.000001
962
192.1.1.11
rep
199429
3306
60
0
，。。。。。。。。启动主从复制：
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.1.1.11
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: Mysql-server-bin.000001
          Read_Master_Log_Pos: 962
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 260
        Relay_Master_Log_File: Mysql-server-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: 962
              Relay_Log_Space: 410
              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: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)测试主从复制功能（省略）

#############sampe 2 测试通过 5.6 版本之前老的方式

https://www.linuxidc.com/Linux/2016-08/134669.htm

mysql主从复制
mysql支持单向双向链式级联实时异步复制，在复制过程中，一台服务器充当主服务器（Master）,而一个或多个其他服务器充当从服务器(Slave)

mysql主从复制的应用场景
1、主从服务器互为备份
2、主从服务器读写分离分担网站压力

读写分离
中大型公司：通过程序(php,java)
测试环境：代理软件(mysql-proxy,amoeba)
门户网站：分布式dbproxy（读写分离，hash负载均衡，健康检查）
主从同步实践操作（多实例环境）

1、主库上面设置server-id值并开启binlog参数
[root@CentOS03 ~]# egrep “log-bin|server-id” /data/3306/my.cnf
log-bin = /data/3306/mysql-bin
server-id = 1


检查实际配置效果
[root@centos03 ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e “show variables like ‘log_bin’;”
+—————+——-+
| Variable_name | Value |
+—————+——-+
| log_bin | ON |
+—————+——-+

2、建立用于同步的账号
mysql> grant replication slave on *.* to rep@’172.16.80.%’ identified by ‘123456’;
说明：replication slave 是mysql同步的必须权限，此处不要授权all

mysql> flush privileges;

查看授权后的结果
mysql> show grants for rep@’172.16.80.%’;
+————————————————————————————————————————–+
| Grants for rep@172.16.80.% |
+————————————————————————————————————————–+
| GRANT REPLICATION SLAVE ON *.* TO ‘rep’@’172.16.80.%’ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9’ |
+————————————————————————————————————————–+
1 row in set (0.00 sec)


3、锁表，导出数据库
mysql> flush table 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.000002 | 332 | | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

[root@centos03 ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B –events –master-data=2 > /opt/rep.sql #导出所有数据库
[root@centos03 ~]# vim /opt/rep.sql
— CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000002′, MASTER_LOG_POS=332; #可以看到该语句的记录位置和上面show master status是一样的，注释状态


4、数据库导出后，解锁
mysql> show master status; #再次查看位置点，以验证上面的锁表操作是否有效
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000002 | 332 | | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

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


5、从库上面
[root@centos03 ~]# egrep “log-bin|server-id” /data/3307/my.cnf
#log-bin = /data/3307/mysql-bin #log-bin无需开启
server-id = 3 #server-id的值不能和主库上面的值一样

[root@centos03 ~]# mysql -uroot -p123456 -S /data/3307/mysql.sock < /opt/rep.sql #导入从主库备份的数据库

[root@centos03 ~]# mysql -uroot -phello123 -S /data/3307/mysql.sock
mysql> change master to \
-> master_host=’172.16.80.118′,\
-> master_user=’rep’,\
-> master_password=’123456′,\
-> master_log_file=’mysql-bin.000002′,\
-> master_log_pos=332;
Query OK, 0 rows affected (0.03 sec)

验证一下
[root@centos03 ~]# cat /data/3307/data/master.info
18
mysql-bin.000002
332
172.16.80.118
rep
123456
3306
60
0

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

mysql> show slave status\G; #观察Slave_IO和Slave_SQL 这两个线程的状态是否是yes
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.80.118
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 332
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
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: 332
Relay_Log_Space: 403
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)

6、登录主库创建数据库，看是否会同步到从库上面
[root@centos03 ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock
mysql> create database martin;
Query OK, 1 row affected (0.01 sec)


观察从库，可以看到已经同步过来
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| martin |
| mysql |
| performance_schema |
| test |
+——————–+
5 rows in set (0.00 sec)


[root@centos03 ~]# cd /data/3307
[root@centos03 3307]# ls
data my.cnf mysql mysqld.pid mysql_martin3307.err mysql.sock relay-bin.000001 relay-bin.000002 relay-bin.index relay-log.info
[root@centos03 3307]# cat data/master.info
18
mysql-bin.000002
419
172.16.80.118
rep
123456
3306
60
0

[root@centos03 3307]# mysqlbinlog relay-bin.000002
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database martin

mysql主从复制原理总结：

1、异步同步方式

2、逻辑同步模式，多种模式，默认是通过sql语句执行

3、主库通过记录bin-log实现对从库的同步，bin-log记录数据库更新的语句

4、主库一个IO线程，从库一个IO线程和一个SQL线程

5、从库关键文件master.info relay-log relay-info

6、如果从库还需要做级联从库，从库需要打开log-bin和log-slave-updates参数

监控mysql主从状态（这里我们简单监控从库上面io和sql线程yes总数为不是2就认为主从出现问题了）

在客户端编写脚本

[root@centos03 tools]# cat /tmp/mysql-replication.sh

#!/bin/bash

/application/mysql/bin/mysql -uroot -p123456 -e ‘show slave status\G’ -S /data/3307/mysql.sock|grep -Ei “Slave_IO_Running|Slave_SQL_Running”|awk ‘{print $2}’|grep -c Yes

修改zabbix-agent配置文件

UnsafeUserParameters=1

UserParameter=mysql.replication,/tmp/mysql-replication.sh

在服务器端添加监控项–触发器–图形

正常情况下的图形如下

此时我们模拟主库挂掉

[root@centos03 tools]# /data/3306/mysql stop

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-08/134669.htm

#### 5.6 版本可以参考 GTID 方式+mysqldump搭建准备复制模式测试通过(数据库比较小推荐这种方式)

https://dev.mysql.com/doc/refman/5.6/en/replication-gtids-howto.html

——=========master———-
一、启用gtid:

1、备份my.cnf文件：
cp /mydb/mysql/app/mysql/my.cnf /mydb/mysql/app/mysql/my.cnf0502
2、master端vi /mydb/mysql/app/mysql/my.cnf加入以下参数：
#GTID
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates = 1

log_bin=mysql-bin

2.2 create rep user

2、建立用于同步的账号
mysql> grant replication slave on *.* to rep@’172.16.80.%’ identified by ‘123456’;
说明：replication slave 是mysql同步的必须权限，此处不要授权all

二、停应用后处理会话
1、应用停止

mysql> SET @@global.read_only = ON;

2、如下查询如有结果则kill掉：
mysql>select concat(‘kill connection ‘, id, ‘;’) from information_schema.processlist;

三、重启

shell> mysqladmin -uusername -p shutdown

mysqld_safe –defaults-file=/**/**/my.cnf &

2.2 use coyp or Mysqldump backup db.

##Mysqldump方式在备份过程中会锁myisam，搭建的时候需要注意主库的活动事物情况。、

##如果是大库，不适用直接拷贝，使用mysqldump比较好。

### master node :

mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B –events –master-data=2 > /opt/rep.sql #导出所有数据库

四、验证参数

–============slave：

一、启用gtid并修改server_id

1、备份my.cnf文件：
cp /mydb/mysql/app/mysql/my.cnf /mydb/mysql/app/mysql/my.cnf0502
2、slave端vi /mydb/mysql/app/mysql/my.cnf加入以下参数：
#GTID
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates = 1

log_bin=mysql-bin

3、vi /mydb/mysql/app/mysql/my.cnf：
server_id=6

二、重启

shell> mysqladmin -uusername -p shutdown

mysqld_safe –defaults-file=/**/**/my.cnf &

mysql -uroot -p123456 -S /data/3307/mysql.sock < /opt/rep.sql #导入从主库备份的数据库

三、验证参数

mysql> show variables like ‘log_bin%’;

四、change master到指定源
change master to
master_host=’xxx’,
master_user=’root’,
master_password=”,
master_port=xxx,
master_auto_position = 1;

五、启动slave同步
1、start slave；

2. show slave status\G

6.Master node

mysql> SET @@global.read_only = off;

############## 5.6 版本可以参考 GTID 方式+ mysqlbackup 搭建准备复制模式测试通过( 数据库比较大推荐这种方式)

目前我的环境都还是mysql5.6的，所以选择用3.12的版本

mysqlbackup企业版在mysql的开源社区是下载不了的，需要有metalink的账号才能下载。（早期是可以下载的，估计后来用户的体验不错，需要授权购买oracle的服务才可以了）

目前我的环境都还是mysql5.6的，所以选择用3.12的版本（3.12的版本修复了几个重要的bug，建议用这个版本）

其他设置命令使用的快捷方式：ln -s /opt/mysql/meb-3.12/bin/mysqlbackup /usr/bin/mysqlbackup

3.1.3 创建单独的备份用户

其他业务分开账号的使用，数据库作为重要的系统，一般都会每年变更管理密码的，这里单独使用一个避免后期还需要变更；
备份用户所需要的基本权限

GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO ‘mysqlbackup’@’localhost’ identified by ‘123456’;;
GRANT CREATE, INSERT, SELECT, DROP, UPDATE ON mysql.backup_history TO ‘mysqlbackup’@’localhost’;
GRANT REPLICATION CLIENT ON *.* TO ‘mysqlbackup’@’localhost’;
GRANT SUPER ON *.* TO ‘mysqlbackup’@’localhost’;
GRANT LOCK TABLES, SELECT, CREATE, DROP, FILE ON *.* TO ‘mysqlbackup’@’localhost’;
GRANT reload on *.* to ‘mysqlbackup’@’localhost’;

###machine 1 primay database:
全备的脚本

#####
[root@db02 backup]# mysqlbackup –user=mysqlbackup –password=123456 –backup-dir=/tmp/dba –with-timestamp –socket /db/mysql/data/mysqltmp/mysql_3307.sock backup

####output
180518 08:26:24 mysqlbackup: INFO: MEB logfile created at /tmp/dba/2018-05-18_08-26-24/meta/MEB_2018-05-18.08-26-24_backup.log

——————————————————————–
Server Repository Options:
——————————————————————–
datadir = /db/mysql/data_3307/
innodb_data_home_dir =
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /db/mysql/data_3307/
innodb_log_files_in_group = 3
innodb_log_file_size = 536870912
innodb_page_size = 16384
innodb_checksum_algorithm = innodb
innodb_undo_directory = /db/mysql/data_3307/
innodb_undo_tablespaces = 0
innodb_undo_logs = 128

——————————————————————–
Backup Config Options:
——————————————————————–
datadir = /tmp/dba/2018-05-18_08-26-24/datadir
innodb_data_home_dir = /tmp/dba/2018-05-18_08-26-24/datadir
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /tmp/dba/2018-05-18_08-26-24/datadir
innodb_log_files_in_group = 3
innodb_log_file_size = 536870912
innodb_page_size = 16384
innodb_checksum_algorithm = innodb
innodb_undo_directory = /tmp/dba/2018-05-18_08-26-24/datadir
innodb_undo_tablespaces = 0
innodb_undo_logs = 128

mysqlbackup: INFO: Unique generated backup id for this is 15266031843454002

mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
180518 08:26:26 mysqlbackup: INFO: Full Backup operation starts with following threads
1 read-threads 6 process-threads 1 write-threads
180518 08:26:26 mysqlbackup: INFO: System tablespace file format is Antelope.
180518 08:26:26 mysqlbackup: INFO: Starting to copy all innodb files…
180518 08:26:26 mysqlbackup: INFO: Copying /db/mysql/data_3307/ibdata1 (Antelope file format).
180518 08:26:26 mysqlbackup: INFO: Found checkpoint at lsn 1626047.
180518 08:26:26 mysqlbackup: INFO: Starting log scan from lsn 1625600.
180518 08:26:26 mysqlbackup: INFO: Copying log…
180518 08:26:26 mysqlbackup: INFO: Log copied, lsn 1626047.
180518 08:26:26 mysqlbackup: INFO: Copying /db/mysql/data_3307/mysql/innodb_index_stats.ibd (Antelope file format).
180518 08:26:26 mysqlbackup: INFO: Copying /db/mysql/data_3307/mysql/innodb_table_stats.ibd (Antelope file format).
180518 08:26:26 mysqlbackup: INFO: Copying /db/mysql/data_3307/mysql/slave_master_info.ibd (Antelope file format).
180518 08:26:26 mysqlbackup: INFO: Copying /db/mysql/data_3307/mysql/slave_relay_log_info.ibd (Antelope file format).
180518 08:26:26 mysqlbackup: INFO: Copying /db/mysql/data_3307/mysql/slave_worker_info.ibd (Antelope file format).
180518 08:26:26 mysqlbackup: INFO: Completing the copy of innodb files.
180518 08:26:26 mysqlbackup: INFO: Starting to copy Binlog files…
180518 08:26:26 mysqlbackup: INFO: Copying /db/mysql/data_3307/mysql-bin.000001.
180518 08:26:26 mysqlbackup: INFO: Copying /db/mysql/data_3307/mysql-bin.000002.
180518 08:26:26 mysqlbackup: INFO: Copying /db/mysql/data_3307/mysql-bin.000003.
180518 08:26:26 mysqlbackup: INFO: Copying /db/mysql/data_3307/mysql-bin.000004.
180518 08:26:26 mysqlbackup: INFO: Copying /db/mysql/data_3307/mysql-bin.000005.
180518 08:26:26 mysqlbackup: INFO: Copying /db/mysql/data_3307/mysql-bin.000006.
180518 08:26:26 mysqlbackup: INFO: Copying /db/mysql/data_3307/mysql-bin.000007.
180518 08:26:27 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.
180518 08:26:27 mysqlbackup: INFO: Starting to lock all the tables…
180518 08:26:27 mysqlbackup: INFO: All tables are locked and flushed to disk
180518 08:26:27 mysqlbackup: INFO: Copying /db/mysql/data_3307/mysql-bin.000008.
180518 08:26:28 mysqlbackup: INFO: Completed the copy of binlog files…
180518 08:26:28 mysqlbackup: INFO: Opening backup source directory ‘/db/mysql/data_3307/’
180518 08:26:28 mysqlbackup: INFO: Starting to backup all non-innodb files in
subdirectories of ‘/db/mysql/data_3307/’
180518 08:26:28 mysqlbackup: INFO: Copying the database directory ‘mysql’
180518 08:26:28 mysqlbackup: INFO: Copying the database directory ‘peng’
180518 08:26:28 mysqlbackup: INFO: Copying the database directory ‘performance_schema’
180518 08:26:29 mysqlbackup: INFO: Copying the database directory ‘test’
180518 08:26:29 mysqlbackup: INFO: Copying the database directory ‘tmp’
180518 08:26:29 mysqlbackup: INFO: Completing the copy of all non-innodb files.
180518 08:26:30 mysqlbackup: INFO: A copied database page was modified at 1626047.
(This is the highest lsn found on page)
Scanned log up to lsn 1626047.
Was able to parse the log up to lsn 1626047.
Maximum page number for a log record 0
180518 08:26:30 mysqlbackup: INFO: All tables unlocked
180518 08:26:30 mysqlbackup: INFO: All MySQL tables were locked for 2.818 seconds.
180518 08:26:30 mysqlbackup: INFO: Reading all global variables from the server.
180518 08:26:30 mysqlbackup: INFO: Completed reading of all global variables from the server.
180518 08:26:30 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /tmp/dba/2018-05-18_08-26-24
180518 08:26:30 mysqlbackup: INFO: Full Backup operation completed successfully.
180518 08:26:30 mysqlbackup: INFO: Backup created in directory ‘/tmp/dba/2018-05-18_08-26-24’
180518 08:26:30 mysqlbackup: INFO: MySQL binlog position: filename mysql-bin.000008, position 1659

————————————————————-
Parameters Summary
————————————————————-
Start LSN : 1625600
End LSN : 1626047
————————————————————-

mysqlbackup completed OK!
[mysql@localhost dba]$

#######

##[root@db02 backup]# mysqlbackup –backup-dir=/backup/2017-09-18_13-49-11 apply-log #因为在备份期间数据库还在读写，把这期间的log进行应用，达到数据的一致性

mysqlbackup –backup-dir=/tmp/dba/2018-05-18_08-26-24 apply-log

####

180518 08:30:51 mysqlbackup: INFO: MEB logfile created at /tmp/dba/2018-05-18_08-26-24/meta/MEB_2018-05-18.08-30-51_apply_log.log

mysqlbackup: INFO: Creating 14 buffers each of size 65536.
180518 08:30:51 mysqlbackup: INFO: Apply-log operation starts with following threads
1 read-threads 1 process-threads
mysqlbackup: INFO: Using up to 100 MB of memory.
180518 08:30:51 mysqlbackup: INFO: ibbackup_logfile’s creation parameters:
start lsn 1625600, end lsn 1626047,
start checkpoint 1626047.
InnoDB: Doing recovery: scanned up to log sequence number 1626047
mysqlbackup: INFO: InnoDB: Starting an apply batch of log records to the database…
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
mysqlbackup: INFO: InnoDB: Setting log file size to 536870912
InnoDB: Progress in MB: 100 200 300 400 500
mysqlbackup: INFO: InnoDB: Setting log file size to 536870912
InnoDB: Progress in MB: 100 200 300 400 500
mysqlbackup: INFO: InnoDB: Setting log file size to 536870912
InnoDB: Progress in MB: 100 200 300 400 500
180518 08:30:55 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to
lsn 1626047.
mysqlbackup: INFO: Last MySQL binlog file position 0 1659, file name mysql-bin.000008:1659
180518 08:30:55 mysqlbackup: INFO: The first data file is ‘/tmp/dba/2018-05-18_08-26-24/datadir/ibdata1’
and the new created log files are at ‘/tmp/dba/2018-05-18_08-26-24/datadir’
180518 08:30:55 mysqlbackup: INFO: Apply-log operation completed successfully.
180518 08:30:55 mysqlbackup: INFO: Full backup prepared for recovery successfully.

mysqlbackup completed OK!
########

从库还原（in standby db）：

machine 2:

mysqlbackup –force –socket=/var/lib/mysql/mysql_3307.sock –datadir=/mysqldb/data_3307 –backup-dir=/tmp/dba/2018-05-18_08-26-24 copy-back

[root@localhost 2018-05-18_08-26-24]# mysqlbackup –force –socket=/var/lib/mysql/mysql_3307.sock –datadir=/mysqldb/data_3307 –backup-dir=/tmp/dba/2018-05-18_08-26-24 copy-back
MySQL Enterprise Backup version 3.12.0 Linux-3.8.13-35.2.1.el7uek.x86_64-x86_64 [2015/03/10]
Copyright (c) 2003, 2015, Oracle and/or its affiliates. All Rights Reserved.

mysqlbackup: INFO: Starting with following command line …
mysqlbackup –force –socket=/var/lib/mysql/mysql_3307.sock
–datadir=/mysqldb/data_3307 –backup-dir=/tmp/dba/2018-05-18_08-26-24
copy-back

mysqlbackup: INFO:
IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful ‘copy-back’ run mysqlbackup
prints “mysqlbackup completed OK!”.

180518 10:18:57 mysqlbackup: INFO: MEB logfile created at /tmp/dba/2018-05-18_08-26-24/meta/MEB_2018-05-18.10-18-57_copy_back.log

mysqlbackup: WARNING: If you restore to a server of a different version, the innodb_data_file_path parameter might have a different default. In that case you need to add ‘innodb_data_file_path=ibdata1:12M:autoextend’ to the target server configuration.
mysqlbackup: WARNING: If you restore to a server of a different version, the innodb_log_files_in_group parameter might have a different default. In that case you need to add ‘innodb_log_files_in_group=3’ to the target server configuration.
mysqlbackup: WARNING: If you restore to a server of a different version, the innodb_log_file_size parameter might have a different default. In that case you need to add ‘innodb_log_file_size=536870912’ to the target server configuration.
——————————————————————–
Server Repository Options:
——————————————————————–
datadir = /mysqldb/data_3307
innodb_data_home_dir = /mysqldb/data_3307
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /mysqldb/data_3307
innodb_log_files_in_group = 3
innodb_log_file_size = 536870912
innodb_page_size = Null
innodb_checksum_algorithm = innodb

mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
180518 10:18:57 mysqlbackup: INFO: Copy-back operation starts with following threads
1 read-threads 1 write-threads
180518 10:18:57 mysqlbackup: INFO: Copying /tmp/dba/2018-05-18_08-26-24/datadir/ibdata1.
180518 10:18:57 mysqlbackup: INFO: Copying /tmp/dba/2018-05-18_08-26-24/datadir/mysql/innodb_index_stats.ibd.
180518 10:18:57 mysqlbackup: INFO: Copying /tmp/dba/2018-05-18_08-26-24/datadir/mysql/innodb_table_stats.ibd.
180518 10:18:57 mysqlbackup: INFO: Copying /tmp/dba/2018-05-18_08-26-24/datadir/mysql/slave_master_info.ibd.
180518 10:18:57 mysqlbackup: INFO: Copying /tmp/dba/2018-05-18_08-26-24/datadir/mysql/slave_relay_log_info.ibd.
180518 10:18:57 mysqlbackup: INFO: Copying /tmp/dba/2018-05-18_08-26-24/datadir/mysql/slave_worker_info.ibd.
180518 10:18:57 mysqlbackup: INFO: Starting to copy Binlog files…
180518 10:18:57 mysqlbackup: INFO: Copying /tmp/dba/2018-05-18_08-26-24/datadir/mysql-bin.000001.
180518 10:18:57 mysqlbackup: INFO: Copying /tmp/dba/2018-05-18_08-26-24/datadir/mysql-bin.000002.
180518 10:18:57 mysqlbackup: INFO: Copying /tmp/dba/2018-05-18_08-26-24/datadir/mysql-bin.000003.
180518 10:18:57 mysqlbackup: INFO: Copying /tmp/dba/2018-05-18_08-26-24/datadir/mysql-bin.000004.
180518 10:18:57 mysqlbackup: INFO: Copying /tmp/dba/2018-05-18_08-26-24/datadir/mysql-bin.000005.
180518 10:18:57 mysqlbackup: INFO: Copying /tmp/dba/2018-05-18_08-26-24/datadir/mysql-bin.000006.
180518 10:18:57 mysqlbackup: INFO: Copying /tmp/dba/2018-05-18_08-26-24/datadir/mysql-bin.000007.
180518 10:18:57 mysqlbackup: INFO: Copying /tmp/dba/2018-05-18_08-26-24/datadir/mysql-bin.000008.
180518 10:18:58 mysqlbackup: INFO: Completed the copy of binlog files…
180518 10:18:58 mysqlbackup: INFO: Copying the database directory ‘mysql’
180518 10:18:58 mysqlbackup: INFO: Copying the database directory ‘peng’
180518 10:18:58 mysqlbackup: INFO: Copying the database directory ‘performance_schema’
180518 10:18:58 mysqlbackup: INFO: Copying the database directory ‘test’
180518 10:18:58 mysqlbackup: INFO: Copying the database directory ‘tmp’
180518 10:18:58 mysqlbackup: INFO: Completing the copy of all non-innodb files.
180518 10:18:58 mysqlbackup: INFO: Copying the log file ‘ib_logfile0’
mysqlbackup: Progress in MB: 200 400
180518 10:19:12 mysqlbackup: INFO: Copying the log file ‘ib_logfile1’
mysqlbackup: Progress in MB: 600 800 1000
180518 10:19:15 mysqlbackup: INFO: Copying the log file ‘ib_logfile2’
mysqlbackup: Progress in MB: 1200 1400
180518 10:19:19 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /mysqldb/data_3307
180518 10:19:19 mysqlbackup: INFO: Copy-back operation completed successfully.
180518 10:19:19 mysqlbackup: INFO: Finished copying backup files to ‘/mysqldb/data_3307’

mysqlbackup completed OK! with 3 warnings

#############

in standby database

chown -R mysql:mysql /mysqldb/data_3307
mysqld_safe –defaults-file=/mysqldb/data_3307/my.cnf &

mysql> show master status;
mysql> reset master;
mysql> show master status;

四、在slave端进行GTID_PURGED，执行backup_gtid_executed.sql中的SET @@GLOBAL.GTID_PURGED语句：

mysql> SET @@GLOBAL.GTID_PURGED=’4e461dbc-54a7-11e8-8589-005056a41701:1-9′;

mysql> change master to
-> master_host=’10.241.95.221′,
-> master_user=’rep’,
-> master_password=’123456′,
-> master_port=3307,
-> master_auto_position = 1;

六、启动slave同步

1.start slave；

2. show slave status\G

####

#####more info about gtids

17.1.3.2 Setting Up Replication Using GTIDs
This section describes a process for configuring and starting GTID-based replication in MySQL 5.6. This is a “cold start” procedure that assumes either that you are starting the replication master for the first time, or that it is possible to stop it; for information about provisioning replication slaves using GTIDs from a running master, see Section 17.1.3.3, “Using GTIDs for Failover and Scaleout”.

The key steps in this startup process for the simplest possible GTID replication topology—consisting of one master and one slave—are as follows:

If replication is already running, synchronize both servers by making them read-only.

Stop both servers.

Restart both servers with GTIDs, binary logging, and slave update logging enabled, and with statements that are unsafe for GTID-based replication disabled. In addition, the servers should be started in read-only mode, and the slave SQL and I/O threads should be prevented from starting on the slave.

The mysqld options necessary to start the servers as described are discussed in the example that follows later in this section.

Instruct the slave to use the master as the replication data source and to use auto-positioning. The SQL statements needed to accomplish this step are described in the example that follows later in this section.

Take a new backup. Binary logs containing transactions without GTIDs cannot be used on servers where GTIDs are enabled, so backups taken before this point cannot be used with your new configuration.

Start the slave, then disable read-only mode again on both servers, so that they can accept updates.

In the following example, two servers are already running as master and slave, using MySQL’s “classic” file-based replication protocol.

Most of the steps that follow require the use of the MySQL root account or another MySQL user account that has the SUPER privilege. mysqladmin shutdown requires either the SUPER privilege or the SHUTDOWN privilege.

Step 1: Synchronize the servers. Make the servers read-only. To do this, enable the read_only system variable by executing the following statement on both servers:

mysql> SET @@global.read_only = ON;
Wait for all ongoing transactions to commit or roll back. Then, allow the slave to catch up with the master. It is extremely important that you make sure the slave has processed all updates before continuing.

If you use binary logs for anything other than replication, for example to do point in time backup and restore, wait until you do not need the old binary logs containing transactions without GTIDs. Ideally, wait for the server to purge all binary logs, and wait for any existing backup to expire.

Important
It is important to understand that logs containing transactions without GTIDs cannot be used on servers where GTIDs are enabled. Before proceeding, you must be sure that transactions without GTIDs do not exist anywhere in the topology.

Step 2: Stop both servers. Stop each server using mysqladmin as shown here, where username is the user name for a MySQL user having sufficient privileges to shut down the server:

shell> mysqladmin -uusername -p shutdown
Then supply this user’s password at the prompt.

Step 3: Restart both servers with GTIDs enabled. To enable binary logging with global transaction identifiers, each server must be started with GTID mode, binary logging, slave update logging enabled, and with statements that are unsafe for GTID-based replication disabled. In addition, you should prevent unwanted or accidental updates from being performed on either server by starting both in read-only mode. This means that both servers must be started with (at least) the options shown in the following invocation of mysqld_safe:

shell> mysqld_safe –gtid_mode=ON –log-bin –log-slave-updates –enforce-gtid-consistency &
Note
Prior to MySQL 5.6.9, –enforce-gtid-consistency was named –disable-gtid-unsafe-statements.

In addition, you should start the slave with the –skip-slave-start option along with the other server options specified in the example just shown.

Note
–gtid-mode is not a boolean, but an enumeration. Use one of the values ON or OFF only, when setting this option. Using a numeric value such as 0 or 1 can lead to unexpected results.

For more information about the –gtid-mode and –enforce-gtid-consistency server options, see Section 17.1.4.5, “Global Transaction ID Options and Variables”.

Depending on your configuration, supply additional options to mysqld_safe or other mysqld startup script.

Step 4: Direct the slave to use the master. Tell the slave to use the master as the replication data source, and to use GTID-based auto-positioning rather than file-based positioning. Execute a CHANGE MASTER TO statement on the slave, using the MASTER_AUTO_POSITION option to tell the slave that transactions will be identified by GTIDs.

You may also need to supply appropriate values for the master’s host name and port number as well as the user name and password for a replication user account which can be used by the slave to connect to the master; if these have already been set prior to Step 1 and no further changes need to be made, the corresponding options can safely be omitted from the statement shown here.

mysql> CHANGE MASTER TO
> MASTER_HOST = host,
> MASTER_PORT = port,
> MASTER_USER = user,
> MASTER_PASSWORD = password,
> MASTER_AUTO_POSITION = 1;
Neither the MASTER_LOG_FILE option nor the MASTER_LOG_POS option may be used with MASTER_AUTO_POSITION set equal to 1. Attempting to do so causes the CHANGE MASTER TO statement to fail with an error.

Step 5: Take a new backup. Existing backups that were made before you enabled GTIDs can no longer be used on these servers now that you have enabled GTIDs. Take a new backup at this point, so that you are not left without a usable backup.

For instance, you can execute FLUSH LOGS on the server where you are taking backups. Then either explicitly take a backup or wait for the next iteration of any periodic backup routine you may have set up.

Step 6: Start the slave and disable read-only mode. Start the slave like this:

mysql> START SLAVE;
Allow the master to begin accepting updates once again by running the following statement:

mysql> SET @@global.read_only = OFF;
GTID-based replication should now be running, and you can begin (or resume) activity on the master as before. Section 17.1.3.3, “Using GTIDs for Failover and Scaleout”, discusses creation of new slaves when using GTIDs.

PREV HOME UP NEXT
User Comments
Sign UpLogin You must be logged in to post a comment.

######issue

error report : MEB 3.12.3 is not compatible with MySQL version >= 5.7.

solution:

Table 1 Compatibility Between MySQL Servers and MySQL Clients/Tools

Client/Tool	Client/Tool Version	MySQL 5.6	MySQL 5.7	MySQL 8.0
MySQL Shell	8.0	✗	✓	✓
MySQL Router	8.0	✗	✓	✓
MySQL for Visual Studio	1.2	✓	✓	✓
MySQL for Visual Studio	2.0	✓	✓	✗
MySQL for Excel	1.3	✓	✓	✓
Oracle Enterprise Manager for MySQL Database	13.2.4.0.0	✓	✓	✓
MySQL Enterprise Backup	3.12	✓	✗	✗
	4.1	✗	✓	✗
	8.0	✗	✗	✓
MySQL Enterprise Monitor	4.0	✓	✓	✗
MySQL Enterprise Monitor	8.0	✓	✓	✓
MySQL Notifier	1.1	✓	✓	✓
MySQL Workbench	8.0	✓	✓	✓

###############22

https://www.cnblogs.com/1477717815fuming/p/8006143.html

公司规模已经形成，用户数据已成为公司的核心命脉，一次老王一不小心把数据库文件删除，通过mysqldump备份策略恢复用了两个小时，在这两小时中，公司业务中断，损失100万，老王做出深刻反省，公司也因此对于数据库的性能和可靠性提出更高要求。要求对数据库进行改造，使其承载力进行提升，故障修复时间减少，有没有能实现的方案呢？

数据库常遇到的问题

一、性能问题

1、向上拓展 scale up ：针对单台服务器，提高服务器的硬件性能，比如：内存，cpu等，个体本身容易达到极限

2、向外拓展 scale out ：多台服务器形成集群，共同完成一件事情

二、可用性问题

1、数据库服务中断

2、误操作数据损坏

3、硬件故障

4、数据库升级测试遭遇bug

5、黑客攻击

数据库高可用技术说明

高可用架构对于互联网服务基本是标配，无论是应用服务还是数据库服务都需要做到高可用。虽然互联网服务号称7*24小时不间断服务，但多多少少有一些时候服务不可用，比如某些时候网页打不开，百度不能搜索或者无法发微博，发微信等。一般而言，衡量高可用做到什么程度可以通过一年内服务不可用时间作为参考，要做到3个9的可用性，一年内只能累计有8个小时不可服务，而如果要做到5个9的可用性，则一年内只能累计5分钟服务中断。所以虽说每个公司都说自己的服务是7*24不间断的，但实际上能做到5个9的屈指可数，甚至根本做不到，国内互联网巨头BAT(百度，阿里巴巴，腾讯)都有因为故障导致的停服问题。对于一个系统而言，可能包含很多模块，比如前端应用，缓存，数据库，搜索，消息队列等，每个模块都需要做到高可用，才能保证整个系统的高可用。对于数据库服务而言，高可用可能更复杂，对用户的服务可用，不仅仅是能访问，还需要有正确性保证，因此，对于实现数据库高可用，对互联网公司来说极其重要！

企业级数据库高可用架构图

Mysql主从架构技术说明

Mysql内建的复制功能是构建大型，高性能应用程序的基础。将Mysql的数据分布到多个系统上去，这种分布的机制，是通过将Mysql的某一台主机（Master）的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。

主从复制架构图

数据库复制特性

Mysql复制解决的问题

MySQL复制技术有以下一些特点：

(1) 数据分布 (Data distribution )

(2) 负载平衡(load balancing)

(3) 备份(Backups)

(4) 高可用性和容错性 High availabilityand failover

Mysql复制如何工作

Mysql的复制功能主要有3个步骤：

(1) 主服务器（master）将改变记录到二进制日志(binarylog)中（这些记录叫做二进制日志事件，binary log events）

(2) 从服务器（slave）将主服务器master的binary logevents拷贝到它的中继日志(relay log)

(3) slave重做中继日志中的事件，将改变反映它自己的数据。

1、该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二进制日志记录这些改变。MySQL将事务串行的写入二进制日志，在事件写入二进制日志完成后，master通知存储引擎提交事务。此后可接收slave的请求

2、下一步就是slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始在主节点上binlog dump process（二进制转存线程）。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。

3、 SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

I/O线程：将master数据库二进制日志拉到slave数据库上，并将二进制日志写到中继日志，写完之后，他会睡眠并等待master数据库二进制日志更新，一旦更新，就会写入slave数据库的中继日志中

SQL线程：读取中继日志的事件，并在数据库中执行，写入到内存中，使slave数据库的数据与master数据库中的数据一致

Mysql实现企业级数据库主从复制架构实战

注意：slave数据库只能是可读的，不能是可写的，如果改变了slave数据库的数据，master不能从slave数据库上同步数据，导致主从数据库数据不一致。

实战演练

一、环境准备

centos系统服务器2台、一台用户做Mysql主服务器，一台用于做Mysql从服务器，都在同一个网段中，配置好yum源、防火墙关闭、各节点时钟服务同步、各节点之间可以通过主机名互相通信

二、准备步骤：

1、iptables -F && setenforce 清空防火墙策略，关闭selinux

2、拿两台服务器都使用yum方式安装Mysql服务，要求版本一致

3、分别启动两台服务器mysql服务，确保服务正常

三、实现步骤:

1、配置master主服务器

对master进行配置，包括打开二进制日志，指定唯一的servr ID。例如，在配置文件加入如下值

vim /etc/my.cnf

server-id=1 #配置server-id，让主服务器有唯一ID号（让从服务器知道他的主服务器是谁）

log-bin=mysql-bin #打开Mysql日志，日志格式为二进制

skip-name-resolve#关闭名称解析，（非必须）

然后重启数据库服务

systemctl restart mariadb

2．创建复制帐号

在Master的数据库中建立一个备份帐户：每个slave使用标准的MySQL用户名和密码连接master

。进行复制操作的用户会授予REPLICATION SLAVE权限。（给从服务器授权，让他能从主服务器拷贝二进制日志）

mysql

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave@’192.168.10.%’ IDENTIFIED BY ‘magedu’;

3．查看主服务器状态

在Master的数据库执行show master status，查看主服务器二进制日志状态

4、配置slave从服务器

对slave进行配置，打开中继日志，指定唯一的servr ID，设置只读权限。在配置文件加入如下值

vim /etc/my.cnf

server-id=2 #配置server-id，让从服务器有唯一ID号

relay_log = mysql-relay-bin #打开Mysql日志，日志格式为二进制

read_only = 1 #设置只读权限

log_bin = mysql-bin #开启从服务器二进制日志

log_slave_updates = 1 #使得更新的数据写进二进制日志中

然后重启数据库服务

systemctl restart mariadb

5．启动从服务器复制线程

让slave连接master，并开始重做master二进制日志中的事件。

mysql

CHANGE MASTER TO MASTER_HOST=’192.168.10.190′,

MASTER_USER=’slave’,

MASTER_PASSWORD=’magedu’,

MASTER_LOG_FILE=’mysql-bin.000001′,

MASTER_LOG_POS=278;

执行start slave；# 启动复制线程。

6、查看从服务器状态

可使用SHOW SLAVE STATUS\G查看从服务器状态，如下所示，也可用show processlist \G查看前复制状态：

mysql

SHOW SLAVE STATUS\G

Slave_IO_Running: Yes #IO线程正常运行

Slave_SQL_Running: Yes #SQL线程正常运行

7.测试

理想的结果是在主服务器上添加的数据，在从服务器上也会同步

在主服务器上

在从服务器上

四、添加新slave服务器

假如master已经运行很久了，想对新安装的slave进行数据同步，甚至它没有master的数据。

此时，有几种方法可以使slave从另一个服务开始，例如，从master拷贝数据，从另一个slave克隆，从最近的备份开始一个slave。为了加快Slave与master同步，可用以下方式先进行数据同步：

(1)master的某个时刻的数据快照；

(2)数据库的备份数据

(3)master的二进制日志文件。

实现主从从架构

也可以搭建主从从架构，让从服务器之间进行复制

就是在从服务器也开启二进制日志，然后从的从I/O线程再将从的二进制日志给拷贝过来写入到自己的relay log中，然后sql线程再读取relay log中的事件，在数据库中执行，写入到内存中。

Mysql复制过滤器

复制过滤器：

仅复制有限一个或几个数据库相关的数据，而非所有；由复制过滤器进行；

有两种实现思路：

(1) 主服务器

主服务器仅向二进制日志中记录有关特定数据库相关的写操作；

binlog_do_db=

binlog_ignore_db=

(2) 从服务器

从服务器的SQL THREAD仅重放关注的数据库或表相关的事件，并将其应用于本地；

Replicate_Do_DB=

Replicate_Ignore_DB=

企业常见数据库架构

一、单一master和多slave

在实际应用场景中，MySQL复制90%以上都是一个Master复制到一个或者多个Slave的架构模式，主要用于读压力比较大的应用的数据库端廉价扩展解决方案。因为只要Master和Slave的压力不是太大（尤其是Slave端压力）的话，异步复制的延时一般都很少很少。尤其是自从Slave端的复制方式改成两个线程处理之后，更是减小了Slave端的延时问题。而带来的效益是，对于数据实时性要求不是特别高的应用，只需要通过廉价的pcserver来扩展Slave的数量，将读压力分散到多台Slave的机器上面，即可通过分散单台数据库服务器的读压力来解决数据库端的读性能瓶颈，毕竟在大多数数据库应用系统中的读压力还是要比写压力大很多。这在很大程度上解决了目前很多中小型网站的数据库压力瓶颈问题，甚至有些大型网站也在使用类似方案解决数据库瓶颈。

单一master和多slave架构图

(1) 不同的slave扮演不同的作用(例如使用不同的索引，或者不同的存储引擎)；

(2) 用一个slave作为备用master，只进行复制；#主服务器挂了之后，可在从服务器执行

1> 在备机上执行STOP SLAVE 和RESET MASTER

2> 查看show slave status \G;

3> 然后修改应用的连接地址。

(3) 用一个远程的slave，用于灾难恢复；

二、互为主从Master-Master(Master-Master in Active-Active Mode)

Master-Master复制的两台服务器，既是master，又是另一台服务器的slave。这样，任何一方所做的变更，都会通过复制应用到另外一方的数据库中。

即：在两台服务器上既执行master的操作又执行slave的操作（注意：两台数据库都必须是可写的）

互为主从复制过程

互为主从：两个节点各自都要开启binlog和relay log；

1、数据不一致；

2、自动增长id；

什么是自增长ID？

对于某些唯一性的字段，可以通过设置自增长ID来实现,自增长ID的数据，代表这个表中存在一条唯一的记录；而自增长id是肯定不会重复的；

创建表，设置ID为自增长

create table userInfo (id int PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL)；

两边插入数据看数据增长

insert into userInfo(name) value(‘xiao’),(‘da’),(‘lao’);

定义一个节点使用奇数id

auto_increment_increment=2 #表示自增长字段每次递增的量

auto_increment_offset=1 #表示自增长字段从那个数开始

另一个节点使用偶数id

auto_increment_increment=2

auto_increment_offset=2

配置：

1、server_id必须要使用不同值；

2、均启用binlog和relay log； read only = 0(因为互为主从，所以必须是可写的)

3、存在自动增长id的表，为了使得id不相冲突，需要定义其自动增长方式；

服务启动后执行如下两步：

4、都授权有复制权限的用户账号；

5、各把对方指定为主节点；

实验：数据库互为主从复制步骤

1.修改mysql配置文件

一台数据库服务器上

vim /etc/my.cnf

server-id = 1

log_bin = mysql_bin

relay_log = relay-log

skip-name-resolve = on

log_slave_updates = 1

auto_increment_increment=2

auto_increment_offset=1

另一台数据库服务器上

vim /etc/my.cnf

server-id = 2

relay_log = relay-log

log_bin = mysql-log

skip-name-resolve = on

log_slave_updates = 1

auto_increment_increment=2

auto_increment_offset=2

修改完配置文件后，重启数据库服务

systemctl restart mariadb

2．创建复制帐号

分别在两台数据库服务器上创建复制账号

mysql

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave@’192.168.10.%’ IDENTIFIED BY ‘magedu’;

3．启动从服务器复制线程

让slave连接master，并开始重做master二进制日志中的事件。

mysql

CHANGE MASTER TO MASTER_HOST=’192.168.10.190′,

MASTER_USER=’slave’,

MASTER_PASSWORD=’magedu’,

MASTER_LOG_FILE=’mysql-bin.000001′,

MASTER_LOG_POS=278;

执行start slave；# 启动复制线程。

另一台数据库服务器也是如此

4、查看从服务器状态

可使用SHOW SLAVE STATUS\G查看从服务器状态，如下所示，也可用show processlist \G查看前复制态：

mysql

SHOW SLAVE STATUS\G

Slave_IO_Running: Yes #IO线程正常运行

Slave_SQL_Running: Yes #SQL线程正常运行

两台数据库服务器都显示如上结果就ok。

5.创建表，设置ID为自增长,两边插入数据看数据增长

在一台数据库服务器上

mysql

create database dnf;

use dnf;

create table userinfo (id int PRIMARY KEY AUTO_INCREMENT,name varchar(20) NOT NULL);

insert into userinfo (name) values(‘ni’),(‘wo’),(‘ta’);

然后查看表，因为是自增长id，从1开始，步长为2，所以添加的数据id为1,3,5

然后在另一台数据库服务器插入数据，因为是自增长id，从2开始，步长为2，所以新添加的数据id为6,8,10

排错：当配置文件中配置中继日志格式不小心配置错了，或者让slave连接master，执行sql语句不小心写错了，都有可能导致start slave；报错，此时可以show slave status\G;会出现一大串信息，里面会提示错误。找到错误以后，重置slave，reset slave;重新设置，然后再start slave；

注意：mysql的错误日志非常重要，可以提供错误信息，从而找到错误原因。

互为主从容易导致数据不一致，此时我们可以用两个实例来互为主从

三种复制方式

异步复制（Asynchronous replication）

MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整

全同步复制（Fully synchronous replication）

指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。需要有超时时间。

半同步复制（Semisynchronous replication）

介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。

半同步复制

支持多种插件：/usr/lib64/mysql/plugins/

需要安装方可使用：

mysql> INSTALL PLUGIN plugin_name SONAME ‘shared_library_name’;

半同步复制：

semisync_master.so

semisync_slave.so

主节点：

INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’;

MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE ‘rpl_semi%’;

+————————————+——-+

| Variable_name | Value |

+————————————+——-+

| rpl_semi_sync_master_enabled | OFF |

| rpl_semi_sync_master_timeout | 10000 |

| rpl_semi_sync_master_trace_level | 32 |

| rpl_semi_sync_master_wait_no_slave | ON |

+————————————+——-+

MariaDB [mydb]> SET GLOBAL rpl_semi_sync_master_enabled=ON/1;

stop slave;

start slave;

从节点：

INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’;

MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE ‘rpl_semi%’;

+———————————+——-+

| Variable_name | Value |

+———————————+——-+

| rpl_semi_sync_slave_enabled | OFF |

| rpl_semi_sync_slave_trace_level | 32 |

+———————————+——-+

MariaDB [mydb]> STOP SLAVE IO_THREAD;

MariaDB [mydb]> SET GLOBAL rpl_semi_sync_slave_enabled = ON ;

MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE ‘rpl_semi%’;

MariaDB [mydb]> START SLAVE IO_THREAD;

stop slave;

start slave;

可查看从库错误日志观察是否生效

master错误日志

slave错误日志

mysql优化：

1.可以用数据缓存，常见的memcache

2.数据库本身有很多缓存机制，可使用对应的缓存策略

3.对数据来说，竟可能使用索引

4.对请求而言，可以实现读写分离，对读请求负载均衡

5.对大数据库或者表，可根据业务逻辑进行分库分表

6.多有的优化，尽可能网内存中存放

分库分表

分库：当数据库的数据非常庞大，可以把数据库分成几个数据库，每个数据库当一类数据，最后在拼接起来

分表：有两种分法：

1.水平拆分：一个表中有10亿条记录，将这10亿条记录分成每10万条记录为一个表

2.垂直拆分：将一个字段或者多个字段分成一个表

http://tieba.baidu.com/p/4558183228

数据库常遇到的问题

一、性能问题

二、可用性问题

数据库高可用技术说明

企业级数据库高可用架构图

Mysql主从架构技术说明

主从复制架构图

数据库复制特性

Mysql复制解决的问题

Mysql复制如何工作

Mysql的复制功能主要有3个步骤：

Mysql实现企业级数据库主从复制架构实战

实战演练

一、环境准备

二、准备步骤：

三、实现步骤:

1、配置master主服务器

2．创建复制帐号

3．查看主服务器状态

4、配置slave从服务器

然后重启数据库服务

5．启动从服务器复制线程

6、查看从服务器状态

7.测试

在从服务器上

四、添加新slave服务器

实现主从从架构

Mysql复制过滤器

企业常见数据库架构

一、单一master和多slave

单一master和多slave架构图

二、互为主从Master-Master(Master-Master in Active-Active Mode)

互为主从复制过程

两边插入数据看数据增长

定义一个节点使用奇数id

配置：

实验：数据库互为主从复制步骤

1.修改mysql配置文件

2．创建复制帐号

3．启动从服务器复制线程

4、查看从服务器状态

5.创建表，设置ID为自增长,两边插入数据看数据增长

三种复制方式

异步复制（Asynchronous replication）

全同步复制（Fully synchronous replication）

半同步复制（Semisynchronous replication）

半同步复制

主节点：

从节点：

mysql优化：

1.可以用数据缓存，常见的memcache

2.数据库本身有很多缓存机制，可使用对应的缓存策略

3.对数据来说，竟可能使用索引

4.对请求而言，可以实现读写分离，对读请求负载均衡

5.对大数据库或者表，可根据业务逻辑进行分库分表

6.多有的优化，尽可能网内存中存放

分库分表

个人收藏笔记记录

开通VIP