学习linux第六十一天

最新推荐文章于 2024-06-13 09:00:30 发布

chiyaoqiang5116

最新推荐文章于 2024-06-13 09:00:30 发布

阅读量122

点赞数

文章标签：数据库网络开发工具

原文链接：https://my.oschina.net/u/3867255/blog/2988797

版权

mysql主从配置

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语句落地

应用场景：

1.从机同步数据，相当于是mysql备份

2.丛机也提供数据读写，相当于是负载均衡

mysql主

[root@hanlin ~]# ps -aux |grep mysql
root 20628 0.0 0.0 113264 1632 pts/0 S 19:10 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data/mysql --pid-file=/usr/local/mysql/data/mysql/hanlin.pid
mysql 20842 0.1 22.5 1304780 458768 pts/0 Sl 19:10 0:05 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=hanlin.err --pid-file=/usr/local/mysql/data/mysql/hanlin.pid --socket=/tmp/mysql.sock --port=3306
root 25307 0.0 0.0 112676 984 pts/0 S+ 20:24 0:00 grep --color=auto mysql
[root@hanlin ~]# netstat -lntp |grep mysql
tcp6 0 0 :::3306 :::* LISTEN 20842/mysqd

[root@hanlin ~]# vim /etc/my.cnf

[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data/mysql
server_id = 12
log_bin=hanlin12

[root@hanlin ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

[root@hanlin ~]# ls -l /usr/local/mysql/data/mysql/
总用量 110744
-rw-rw----. 1 mysql mysql 56 11月 22 15:25 auto.cnf
-rw-rw----. 1 mysql mysql 120 12月 14 20:42 hanlin12.000001
-rw-rw----. 1 mysql mysql 18 12月 14 20:42 hanlin12.index
-rw-rw----. 1 mysql mysql 118959 12月 14 20:42 hanlin.err
-rw-rw----. 1 mysql mysql 6 12月 14 20:42 hanlin.pid
-rw-rw----. 1 mysql mysql 12582912 12月 14 20:42 ibdata1
-rw-rw----. 1 mysql mysql 50331648 12月 14 20:42 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 11月 22 15:11 ib_logfile1
drwx------. 2 mysql mysql 4096 12月 14 19:09 mysql
drwx------. 2 mysql mysql 48 12月 5 21:46 mysql2
drwx------. 2 mysql mysql 4096 12月 14 19:09 performance_schema
drwx------. 2 mysql mysql 6 11月 22 15:11 test

[root@hanlin mysql]# mysqldump -uroot mysql > /tmp/sql.bak
[root@hanlin mysql]# mysql -uroot -e "create database xuyao"
[root@hanlin mysql]# mysqldump -uroot xuyao < /tmp/sql.bak
-- MySQL dump 10.13 Distrib 5.6.39, for linux-glibc2.12 (x86_64)
--
-- Host: localhost Database: xuyao
-- Dump completed on 2018-12-14 21:27:01
mysql> grant replication slave on *.* to 'repl'@192.168.0.222 identified by '123456a.';
Query OK, 0 rows affected (0.00 sec)

mysql>flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| hanlin12.000001 | 630 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

[root@hanlin mysql]# mysqldump -uroot test >/tmp/test.sql
[root@hanlin mysql]# mysqldump -uroot xuyao >/tmp/xuyao.sql
[root@hanlin mysql]# mysqldump -uroot mysql2 > /tmp/my2.sql

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

mysql从

[root@xuyao logs]# ps -aux |grep mysql
root 9500 0.0 0.1 113264 1376 pts/0 S 12月13 0:00 /bin/sh /usr/local/mysq/bin/mysqld_safe --datadir=/usr/local/mysql/data/mysql --pid-file=/usr/local/mysql/data/mysql/xuyao.pid
mysql 9713 0.2 45.1 1305792 451576 pts/0 Sl 12月13 3:02 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=xuyao.err --pid-file=/usr/local/mysql/data/mysql/xuyao.pid --socket=/tmp/mysql.sock --port=3306
root 13441 0.0 0.0 112676 984 pts/0 R+ 20:24 0:00 grep --color=auto mysql
[root@xuyao logs]# netstat -lntp |grep mysql
tcp6 0 0 :::3306 :::* LISTEN 9713/mysqld

[root@xuyao logs]#vim /etc/my.cnf

server_id = 222
[root@xuyao logs]#/etc/init.d/mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL.. SUCCESS!

[root@xuyao mysql]# scp 192.168.0.12:/tmp/*.sql /tmp
root@192.168.0.12's password:
my.sql 100% 643KB 47.0MB/s 00:00
mysql.bak.sql 100% 1807 895.3KB/s 00:00
per.sql 100% 802 1.3MB/s 00:00
t1.sql 100% 203 185.7KB/s 00:00
test.sql 100% 1259 1.8MB/s 00:00
user.sql 100% 785 841.4KB/s 00:00
xuyao.sql 100% 1260 1.9MB/s 00:00

my2.sql 100% 1812 722.6KB/s 00:00

[root@xuyao mysql]# mysqldump -uroot xuyao </tmp/xuyao.sql

[root@xuyao mysql]# mysqldump -uroot mysql2 </tmp/my2.sql

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host='192.168.0.12', master_user='repl', master_password='12 3456a.', master_log_file='hanlin12.000001', master_log_pos=630;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
[root@xuyao mysql]# mysql -uroot
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.12
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: hanlin12.000001
Read_Master_Log_Pos: 630
Relay_Log_File: xuyao-relay-bin.000002
Relay_Log_Pos: 282
Relay_Master_Log_File: hanlin12.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: 630
Relay_Log_Space: 455
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: 12
Master_UUID: be02a127-ee27-11e8-ae7e-000c292d31e9
Master_Info_File: /usr/local/mysql/data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)

mysql主从测试

从上执行mysql -uroot
show slave stauts\G
看是否有
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
还需关注
Seconds_Behind_Master: 0 //为主从延迟的时间
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

主机：

mysql> use mysql2;
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_mysql2 |
+------------------+
| t1 |
+------------------+
1 row in set (0.00 sec)

mysql> select count (*) from t1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) from t1' at line 1
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)

从机：

其他参数

主服务器上
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=

如果两边数据不一致，就要重新做主从，从机不能单独误删，不然就要在保证主从一致的情况下，重新做change_master

转载于:https://my.oschina.net/u/3867255/blog/2988797

chiyaoqiang5116

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
学习linux第六十一天

mysql主从配置 MySQL 主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步的 MySQL主从是基于binlog的，主上须开启binlog才能进行主从。主从过程大致有3个步骤 1）主将更改...
复制链接

扫一扫