17-day47&48-mysql Master-Slave configuration
17.1 MySQL Master-slave introduction
MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后,在A上写数据,另外一台B也会跟着写数据,两者数据实时同步的MySQL主从是基于binlog的,主上须开启binlog才能进行主从。主从过程大致有3个步骤1)主将更改操作记录到binlog里2)从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里3)从根据relaylog里面的sql语句按顺序执行主上有一个log dump线程,用来和从的I/O线程传递binlog从上有两个线程,其中I/O线程用来同步主的binlog并生成relaylog,另外一个SQL线程用来把relaylog里面的sql语句落地
17.2 preparatory work
Operation Process
cd /usr/local/src wget https://mirrors.tuna.tsinghua.edu.cn/mysql/downloads/MySQL-5.6/mysql-5.6.39-linux-glibc2.12-x86_64.tar.gz tar zxvf mysql-5.6.39-linux-glibc2.12-x86_64.tar.gz mv mysql-5.6.39-linux-glibc2.12-x86_64 /usr/local/mysql cd /usr/local/mysql useradd -s /sbin/nologin mysql (或 useradd -r -m -d /data/mysql -s /sbin/nologin mysql ) mkdir /data/mysql ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql cp support-files/my-default.cnf /etc/my.cnf cp support-files/mysql.server /etc/init.d/mysqld vim /etc/my.cnf 定义 datadir&socket [mysqld] datadir=/data/mysql socket=/tmp/mysql.sock vi /etc/init.d/mysqld 定义 basedir 和 datadir basedir=/usr/local/mysql datadir=/data/mysql chmod 755 /etc/init.d/mysqld /etc/init.d/mysqld start
17.3 Configuring the Master
Master-Slave configuration-Lord operation
安装mysql修改my.cnf,增加server-id=130和log_bin=aminglinux1修改完配置文件后,启动或者重启mysqld服务把mysql库备份并恢复成aming库,作为测试数据mysqldump -uroot mysql > /tmp/mysql.sqlmysql -uroot -e “create database aming”mysql -uroot aming < /tmp/mysql.sql创建用作同步数据的用户grant replication slave on *.* to ‘repl‘@slave_ip identified by ‘password‘;flush tables with read lock;show master status;
Operation Process
[[email protected] ~]# vim/etc/my.cnf[mysqld]datadir=/data/mysqlsocket=/tmp/mysql.sockserver-id=160log_bin= Aminglinux01[[email protected] ~]#/etc/init.d/mysqld restart[[email protected] mysql]# cd/data/mysql/[[ Email protected] mysql]# ls-lt total dosage 110680-rw-rw----. 1 mysql mysql 50331648 April 05:01 ib_logfile0-rw-rw----. 1 mysql mysql 12582912 April 05:01 ibdata1-rw-rw----. 1 mysql mysql 55008 April 05:01 linux-01.err-rw-rw----. 1 MySQL MySQL 6 April 05:01 linux-01.pid-rw-rw----. 1 MySQL mysql 22 April 05:01 AMINGLINUX01.INDEX-RW-RW----. 1 MySQL mysql 120 April 05:01 aminglinux01.000001drwx------. 2 mysql mysql 4096 April 05:39 zrlogdrwx------. 2 mysql mysql 4096 March 21:32 mysql2-rw-rw----. 1 MySQL mysql 56 March 06:01 auto.cnfdrwx------. 2 mysql mysql 4096 March 05:56 mysqldrwx------. 2 mysql mysql 4096 March 05:56 performance_schema-rw-rw----. 1 mysql mysql 50331648 March 05:55 ib_logfile1drwx------. 2 MYSQL MySQL 6 March 05:55 test[[email protected] mysql]# mysqldump-uroot-paminglinux mysql >/tmp/mysql.sqlw Arning:using a password on the command line interface can is insecure. [[email protected] mysql]# mysql-uroot-paminglinux-e "CREATE Database aming" warning:using a password on the comma ND line interface can insecure. [[email protected] mysql]# mysql-uroot-paminglinux aming </tmp/mysql.sqlwarning:using a password on the Comman D line interface can insecure. [[email protected] mysql]# mysql-uroot-paminglinuxmysql> grant replication Slave on * * to ' repl ' @ ' 192.168.106. 165 ' identified by ' aminglinux111 '; Query OK, 0 rows Affected (0.00 sec) 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 | Executed_gtid_set |+---------------------+----------+--------------+------------------+-------------------+| aminglinux01.000001 | 669865 | | | |+---------------------+----------+--------------+------------------+-------------------+1 row in Set (0.00 sec) [[ Email protected] mysql]# ls-lht total dosage 222m-rw-rw----. 1 mysql mysql 655K April 06:21 aminglinux01.000001-rw-rw----. 1 mysql mysql 48M April 06:19 ib_logfile0-rw-rw----. 1 mysql mysql 76M April 06:19 ibdata1drwx------. 2 mysql mysql 4.0K April 06:19 amingdrwxr-x---. 2 root root 19 April 05:09 LOGS-RW-RW----. 1 mysql mysql 54K April 05:01 linux-01.err-rw-rw----. 1 MySQL MySQL 6 April 05:01 linux-01.pid-rw-rw----. 1 MySQL mysql 22 April 05:01 aminglinux01.indexdrwx------. 2 mysql mysql 4.0K April 05:39 zrlogdrwx------. 2 mysql mysql 4.0K March 21:32 mysql2-rw-rw----. 1 MySQL mysql 56 March 06:01 auto.cnfdrwx------. 2 mysql mysql 4.0K March 05:56 mysqldrwx------. 2 mysql MySQL 4.0K March 05:56 performANCE_SCHEMA-RW-RW----. 1 mysql mysql 48M March 05:55 ib_logfile1drwx------. 2 MySQL MySQL 6 March 05:55 test[[email protected] mysql]# mysqldump-uroot-paminglinux zrlog >/tmp/zrlog.sq Lwarning:using a password on the command line interface can is insecure. [[email protected] mysql]# mysqldump-uroot-paminglinux mysql2 >/tmp/mysql2.sqlwarning:using a password on the Command line interface can insecure. [[email protected] mysql]# mysqldump-uroot-paminglinux test >/tmp/test.sqlwarning:using a password on the Comm and line interface can insecure.
17.4 Configuration from
Master-Slave configuration-operation from top
安装mysql查看my.cnf,配置server-id=132,要求和主不一样修改完配置文件后,启动或者重启mysqld服务把主上aming库同步到从上可以先创建aming库,然后把主上的/tmp/mysql.sql拷贝到从上,然后导入aming库mysql -urootstop slave;change master to master_host=‘‘, master_user=‘repl‘, master_password=‘‘, master_log_file=‘‘, master_log_pos=xx,start slave;还要到主上执行 unlock tables
See if master-slave synchronization is working
从上执行mysql -urootshow slave stauts\G看是否有Slave_IO_Running: YesSlave_SQL_Running: Yes还需关注Seconds_Behind_Master: 0 //为主从延迟的时间Last_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:
Operation Process
From
[[email protected] ~]# Vim/etc/my.cnf[mysqld]datadir=/data/mysqlsocket=/tmp/mysql.sockserver-id=165[[email protected] ~]#/etc/init.d/mysqld restartshutting down MySQL. success! Starting MySQL. success! [[email protected] ~]# SCP 192.168.106.160:/tmp/*.sql/tmp/[email protected] ' s password:mysql2.sql 100% 645KB 31.7mb/s 00:00 zrlog.sql 100% 11KB 5.5mb/s 00:00[[email protected] ~]# alias ' Mysql=/usr/local/mysql/bin/mysql ' [[EMA Il protected] ~]# alias ' mysqldump=/usr/local/mysql/bin/mysqldump ' [[email protected] ~]# Mysql-urootmysql > CREATE Database aming; Query OK, 1 row Affected (0.00 sec) mysql> CREATE database Zrlog; Query OK, 1 row Affected (0.00 sec) mysql> CREATE database mysql2; Query OK, 1 row Affected (0.00 sec) mysql> quit[[email protected] ~]# mysql-uroot mysql2 </tmp/mysql2.sql [[em Ail protected] ~]# mysql-uroot Zrlog </tmp/zrlog.sql [[email protected] ~]# mysql-urootmysql> stop slave; Query OK, 0 rows affected, 1 Warning (0.00 sec) mysql> Change master to master_host= ' 192.168.106.160 ', master_user= ' repl ' , master_password= ' aminglinux111 ', master_log_file= ' aminglinux01.000001 ', master_log_pos=669865; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows Affected (0.00 sec) mysql> Show slave status\g*************************** 1. Row *************************** slave_io_running:connecting Slave_sql_running:yes
Main
[Email protected] ~]# Mysql-uroot-paminglinux
mysql> unlock tables;Query OK, 0 rows affected (0.00 sec)
17.5 testing master-Slave synchronization
Several configuration parameters
主服务器上 binlog-do-db= //仅同步指定的库 binlog-ignore-db= //忽略指定库 从服务器上 replicate_do_db= replicate_ignore_db= replicate_do_table= replicate_ignore_table= 以上四项不建议使用 replicate_wild_do_table= //如aming.%, 支持通配符% replicate_wild_ignore_table=
Test Master
主上 mysql -uroot aming select count(*) from db;truncate table db;到从上 mysql -uroot amingselect count(*) from db;主上继续drop table db;从上查看db表
Operation Process
mysql> use aming;
Reading table information for completion of table and column names
Can turn off this feature to get a quicker startup with-a
Database changed
Mysql> Show tables;
+---------------------------+
| tables_in_aming |
+---------------------------+
| Columns_priv |
| db |
| Event |
| Func |
| General_log |
| Help_category |
| Help_keyword |
| help_relation |
| Help_topic |
| Innodb_index_stats |
| Innodb_table_stats |
| Ndb_binlog_index |
| Plugin |
| Proc |
| Procs_priv |
| Proxies_priv |
| Servers |
| Slave_master_info |
| Slave_relay_log_info |
| Slave_worker_info |
| Slow_log |
| Tables_priv |
| Time_zone |
| Time_zone_leap_second |
| Time_zone_name |
| time_zone_transition |
| Time_zone_transition_type |
| user |
+---------------------------+
Rows in Set (0.00 sec)
Mysql> Select COUNT (*) user;
+------+
| user |
+------+
| 1 |
+------+
1 row in Set (0.00 sec)
2018-05-16 Linux Learning