头条新闻

鬼故事短篇超吓人,Mysql(Mariadb)数据库主从复制,滴水观音

Mysql(Mariadb)数据库主从仿制

Mysql主从仿制的完结原理图大致如下:

MySQL之间数据仿制的根底是以二进制日志文件(binary log file)来完结的,一台MySQL数据库一旦启用二进制日志后,其作为master,它数据库中一切操作都会以“事情”的方法记载在二进制日志中,其他数据库作为slave经过一个I/O线程与主效劳器坚持通鬼故事短篇超吓人,Mysql(Mariadb)数据库主从仿制,滴水观消息,并监控master的二进制日志文件的改变,假如发现master二进制日志文件发生改变,则会把改变仿制到自己的中继日志中,然后slave的一个SQL线程会把相关的“事情”履行到自己的数据库中,以此完结从数据库和主数据库的共同性,也就完结了主从仿制。MySQL(MariaDB)详细详细的装置能够参阅《Linux就该这么学》教程的第十八章节,里边内容写的非我想做海鲜倒腾的生意常详细,合适初学者,本文也比较合适企业运用。

完结MySQL主从仿制装备要求:

主效劳器:1、敞开数据库二进制日志功用;2、装备数据库认证仅有用劳id;3、取得主库的二进制日志文件名及方位;4、在主库上面创立一个用于主库和从库通讯的用户账号,安全办理。

从效劳器:1、在从库中装备仅有用劳id;2、运用主库创立分配的用户账号读取主库的二进制日志;3、启用slave功用,用于主从通讯。

一白雪心心奈、准备工作:

1.主从数据库版别最好共同;

2.主从数据库内数据坚持共同;

主数据库(master):192.168.3.91 /CentOS Linux release 7.5姐妹五月天.1804 (Core)

从数据库( slave ) :192.168.3.218 /CentOS Linux release 7.5.1804 (Core)

留意:这儿的主从都是经过yum源装置的mariadb 5.5.撒农大陆56;

# yum install mariadb-server.x86_64 mariadb.x86_64 -y

//设置mariadb效劳

# systemctl start mariadb.service &&鬼故事短篇超吓人,Mysql(Mariadb)数据库主从仿制,滴水观音 systemctl enable mariadb.service

//设置mariadb数据库root账号的暗码,默许root用户是没有暗码;

# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB

SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFUL鬼故事短篇超吓人,Mysql(Mariadb)数据库主从仿制,滴水观音LY!

In order to log into MariaDB to secure it, we'll need the current

password for the root user. Ifc菌到底是男是女 you've just installed MariaDB, and

you haven't set the root password yet, the password will be blank,

so you should just press enter here.

Enter current password for root (enter for none):

OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB

root user without the proper authorisation.

Set root password? [Y/n] y

New password:

Re-enter new password:

Password updated successfully!

Reloading privilege tables..

... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone

to log into MariaDB without having to have a user account created for

them. This is intended only for testing, and to make the installation

go a bit smoother. You should remove them before moving into a

production environment.

Remove anonymous users? [Y/n] y

... Success!

Normally, root should only be allowed to connect from 'localhost'. This

ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n

... skipping.

By default, MariaDB comes with a database named 'test' that anyone can

access. This is also intended only for testing, and should be removed

before moving into a production environment.

Remove test database and access to it? [Y/n] n

... skipping.

Reloading the privileg鬼故事短篇超吓人,Mysql(Mariadb)数据库主从仿制,滴水观音e tables will ensure that all changes made so far

will take effect immediately.

Reload privilege tables now? [Y/n] y

... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MariaDB

installation should now be secure.

Thanks for using MariaDB!

二、主数据库master修正:

1.修正mysql装备

找到主数据库的装备文件my.cnf(或许my.ini),我的在/etc/my.cnf,在[mysqld]部分刺进如下两行:

# find / -name my.cnf

默许装备

[mysqld]log-bin=mysql-bin #敞开二进制日志 server-id=1 #设置server-id

log-bin="/var/lib/mysql/" #设定生成的log文件名;

修正后:

# systemctl restart mariadb.service

2.重启mysql,创立用于同步的用户账号

# mysql -hlocalhost -uroot -ppassword

创立用户并授权:用户:wxp,暗码:password

MariaDB [(none)]> CREATE USER 'wxp'@'192.168.3.218' IDENTIFIED BY 'password';#创立用户

MariaDB [(none)]> GRANT REPLICATION SLAVE ON 秦勉个人资料*.* TO 'wxp'@'192.168.3.218';#分配权限

MariaDB [(none)]>flush privileges; #改写权限

3.检查master状况,记载二进制文件名(mysql-bin.000001)和方位(492):

MariaDB [(none)]> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 49奥施康定多少钱一盒2 | | |

+------------------+----------+--------------+---赵鑫妤---------------+

1 row in set (0.00 sec)

二、从效劳器slave修正:

1.修正mysql装备

相同找到my.cnf装备文件,增加server-id

# find / -name my.cnf

my.cnf默许装备

[mysqld]server-id=2 #设置server-id,有必要仅有

log-bin="/var/lib/mysql/" #设定生成的log文件名;

修正后:

# systemctl restart mariadb.ser鬼故事短篇超吓人,Mysql(Mariadb)数据库主从仿制,滴水观音vice

2.重启mysql,翻开mysql会话,履行同步SQL句子(需求主效劳器主机名,登陆凭证,二进制佛说做人文件的称号和方位):

# mysql -hlocalhost -uroot -ppassword

MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.3.91', -> MASTER_USER='wxp', -> MASTER_PASSWORD='pas征战暗国际sword', -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=492;

这儿是直接把信息写入到数据库里边,

mysql> select * from mysql.slave_master_info \G

3.发动slave同步进程:

MariaDB [(none)]>start slave;

4.检查slave状况:

MariaDB [(none)]> show slave status\G;

MariaDB [(none)]> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.3.91

Master_User: wxp

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read鬼故事短篇超吓人,Mysql(Mariadb)数据库主从仿制,滴水观音_Master_Log_Pos: 492

Relay_Log_File: mariadb-relay-bin.000002

Relay_Log_Pos: 529

Relay_Master_Log_File: mysql-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: 492

Relay_Log_Space: 825

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master拐点看盘_SSL_Allowed: No

Master_SSL_CA_File:

M残隼aster_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Ma小彩旗老公ster_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)

ERROR: No query specified

当Slave_IO_Running和Slave_SQL_Running都为YES的时分就表明主从同步设置成功了。接下来就能够进行一些验证了,比如在主master数据库的test数据库的一张表中刺进一条数据,在slave的test库的相同数据表中检查是否有新增的数据即可验证主从仿制功用是否有用,还能够封闭slave(Mari宋敬辉aDB [(none)]>stop slave;),然后再修正master,看slave是否也相应修正(中止slave后,master的修正不会同步辣妹曾根到slave),就能够完结主从仿制功用的验证了。

5、测验,操作Master数据库

MariaDB [(none)]> use test;

Database changed

MariaDB [test]> create table t1(Name varchar(18hkbdsmc));

Query OK, 0 rows affected (0.03 sec)

MariaDB [test]> insert into t1(Name) values('wxp');

Query OK, 1 row affected (0.01 sec)

MariaDB [test]> select * from t1;

+------+

| Name |

+------+

| wxp |

+------+

1 row in set (0.00 sec)

在slave上面检查test库是否有数据同步过来;

[root@backup-3-218 ~]# mysql挖金网 -hlocalhost -uroot -ppassword

MariaDB [(none)]> use test;

MariaDB [test]> show tables;

+----------------+

| Tables_i悦楽之胤n_test |

+----------------+

| t1 |

+----------------+

1 row in set (0.00 sec)

MariaDB [test]> select * from t1;

+------+

| Name |

+------+

| wxp |

+------+

1 row in set (0.00 sec)

6、还能够用鬼故事短篇超吓人,Mysql(Mariadb)数据库主从仿制,滴水观音到的其他相关参数:

master敞开二进制日志后默许记载一切库一切表的操作,能够经过装备来指定只记载指定的数据库乃至指定的表的操作,详细在mysql装备文件的[mysqld]可增加修正如下选项:

# 不同步哪些数据库

# vim /etc/my.cnf binlog-ignore-db = mysql binlog-ignore-db = test binlog-ignore-db = information_schema

# systemctl restart mariadb.service

# 只同步哪些数据库,除此之外,其他不同步 binlog-do-db = wxp

# 日志保存时刻

expire_logs_days飞亚达制衣厂 = 10

# 操控binlog的写入频率。每履行多少次业务写入一次

# 这个参数功能耗费很大,但可减小MySQL溃散形成的丢失

sync_binlog = 5

# 日志格局,主张mixed

# statement 保存SQL句子

# row 保存影响记载数据

# mixed 前面两种的结合

binlog_format = mixed

在slave数据库上面操作,设置从头衔接超时时刻

# 中止主从同步

mysql> stop slave;

# 衔接断开时,从头衔接超时时刻

mysql> change master to master_connect_retry=50;

# 敞开主从同步

mysql> start slave;

推荐新闻