海宁天气预报,Mysql(Mariadb)数据库主从复制,001417

频道:国内时事 日期: 浏览:166

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

MySQL之间数据仿制的根底是以二天津平行进口车命案进制日志文件(binary log file)来完结的,一台MySQL数据库一旦启用二进制日志后,其作为master,它数据库中一切操作都会以“事情”的方法记载在二进制日志中,其他数据库作为slave经过一个I/O线程与主效劳器坚持通讯,并监控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;

# yuminstallmariadb-server.x86_64mariadb.x86_64-y

//设置mariadb效劳

# systemctlstartmariadb.s好易购电视直播ervice&& systemctlenablemariadb.service

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

# mysql_secure_installation

NOTE: RUNNING ALL PARTS OFTHIS ISRECOMMENDED FORALL MariaDB

SERVERS INPRO哈利油传全集DUCTION USE! PLEASE READ EACHSTEPCAREFULLY!

Inordertolog intoMariaDB tosecure it, we 'll need the current

password forthe root user. Ifyou '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 forroot (enter fornone):

OK, successfully used password, moving on...

Setting the焦安博 root password ensures that nobody can log intothe MariaDB

root user without the p北海海景彩云宾馆roper authorisation.

Setroot password? [Y/n] y

Newpassword:

Re-enter newpassword:

Password updated successfully!

Reloading privilege tables..

... Succes海宁天气预报,Mysql(Mariadb)数据库主从仿制,001417s!

Bydefault, a MariaDB installation has an anonymous user, 海宁天气预报,Mysql(Mariadb)数据库主从仿制,001417allowing anyone

tolog intoMariaDB without having tohave a user account created for

them. This isintended only fortesting, andtomake the installation

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

production environment.

Remove anonymous users? [Y/n] y

... Success!

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

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

Disallow root login remotely? [Y/n] n

... skipping.

Bydefault, MariaDB comes witha database named 'test' that anyone can

access. This isalso intended only fortesting, andshould be removed

before moving intoa production environment.

Remove test database andaccess toit? [Y/n] n

... skipping.

Reloading the privilege tables will ensure that all changes made so far

will takeeffect immediately.

Reload privilege tables now? [Y/n] y

... Success!

Cle海宁天气预报,Mysql(Mariadb)数据库主从仿制,001417aning up...

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

installation should now be secure.

Thanks forusingMariaDB!

二、主数据库master修正:1.修正mysql装备

找到主数据库的海宁天气预报,Mysql(Mariadb)数据库主从仿制,001417装备文件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)]> C瞋目切齿REATE 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| 492 || |

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

1row inset ( 0. 00sec)

三、从效劳器s薇依笙lave修正:

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.service

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

MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST= '192.168.3.91', -> MASTER_USER= 'wxp', -> MASTER_PASSWORD= 'password', -> MASTER_LOG_FILE= 'mysql-bin.000001', -> MASTER_LOG_POS= 492;

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

mysql> select* frommysql.slave_master_info G

3.发动slave同步进程:MariaDB[( none)]>start slave;

4.检查slave状况:

MariaDB [(none)]> show slave statusG;

MariaDB [(none)]> show slave statusG;

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

Slave _IO_State: Waiting for master to send event

Master_Host: 192.1磁力把68.3.91

Master福利区_User: wxp

Master_Port: 3306

Connect_Retry: 60

Master _Log_File: mysql-bin.000001

Read _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:

Master _SSL_CA_Path:

Master _SSL_Cert: kreayshawn

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)

ERROR: No query specified

当Sl海宁天气预报,Mysql(Mariadb)数据库主从仿制,001417ave_IO_Running和Slave_SQL_Ru海宁天气预报,Mysql(Mariadb)数据库主从仿制,001417nning都为YES的时分就表明主从同步设置成功了。接下来就能够进行一些验证了,比如在主master数据库的test数据库的一张表中刺进一条数据,在slave的test库的相同数据表中检查是否有新增的数据即可验证主从仿制功用是否有用,还能够封闭slave(MariaDB [(none)]>stop slave;)赤松贞明,然后再修正master,看slave是否也相应修正(中止slave后,master的修正不会同步到slave),就能够完结主从仿制功用的验证了。

5、测验,操作Master数据库MariaDB [(none)]> use test;

Database changed

MariaDB [test]> createtablet1(Name varchar( 18));

Query OK, 0rows affected ( 0.03sec)

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

Query OK, 1row affected ( 0.01sec)

MariaDB [test]> select* from t1;

+ ------+

| Name |

+ ------+

| wxp |

+ ------+

1row inset ( 0.00sec)

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

[root@backup -3-218~]# mysql -hlocalhost -uroot -ppasswor张紫妍生前被逼玩5pd

MariaDB [(none)]> use test;

MariaDB [test]> show tables;乱文

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

| Tables_in_te重生之超级红三代周凡st |

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

| t1 |

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

1row inset ( 0孽乱青石沟.00sec)

MariaDB [test]> select* from t1;

+ ------+

| Name |

+ ------+

| wxp |

+ 阴亲------+

1row inset ( 0.00sec)

6、还能够用到的其他相关参数:

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

# 不同步哪些数据库

# vim /etc/my.cnf

binlog-ignore-db= mysql

binlog-ignore-db= test

binlog-ignore-db= information_schema

# systemctl restart海宁天气预报,Mysql(Mariadb)数据库主从仿制,001417 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;

Master AR
声明:该文观念仅代表作者自己,搜狐号系信息发布渠道,搜狐仅供给信息存储空间效劳。
热门
最新
推荐
标签