2018-05-16 Linux Learning

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.