简单配置mysql主从_mysql主从简单配置

﹏ヽ暗。殇╰゛Y 2022-10-24 11:59 211阅读 0赞

环境

hostname

端口

master

128.0.0.101

3306

slave

128.0.0.102

3306

安装mysql

\[root@master~\]\#yum-y install mysql mysql-server mysql-devel

\[root@slave~\]\#yum-y install mysql mysql-server mysql-devel

启动mysql

\[root@master~\]\#service mysqld start

InitializingMySQLdatabase:WARNING:Thehost'master'could not be looked up with resolveip.

Thisprobably means that your libc libraries are not100%compatible

with this binaryMySQLversion.TheMySQLdaemon,mysqld,should work

normally with the exception that host name resolving will not work.

Thismeans that you should use IP addresses instead of hostnames

when specifyingMySQLprivileges!

InstallingMySQLsystem tables...

Fillinghelp tables...

Tostart mysqld at boot time you have to copy

support-files/mysql.server to the right placeforyour system

PLEASE REMEMBER TO SET A PASSWORD FOR THEMySQLroot USER!

Todoso,start the server,thenissue the following commands:

/usr/bin/mysqladmin-u root password'new-password'

/usr/bin/mysqladmin-u root-h master password'new-password'

Alternativelyyou can run:

/usr/bin/mysql\_secure\_installation

which will also give you the option of removing the test

databases and anonymous user created by default.Thisis

strongly recommendedforproduction servers.

Seethe manualformore instructions.

Youcan start theMySQLdaemon with:

cd/usr;/usr/bin/mysqld\_safe&

Youcan test theMySQLdaemon with mysql-test-run.pl

cd/usr/mysql-test;perl mysql-test-run.pl

Pleasereport any problems with the/usr/bin/mysqlbug script!

\[OK\]

Startingmysqld:\[OK\]

\[root@slave~\]\#service mysqld start

InitializingMySQLdatabase:WARNING:Thehost'slave'could not be looked up with resolveip.

Thisprobably means that your libc libraries are not100%compatible

with this binaryMySQLversion.TheMySQLdaemon,mysqld,should work

normally with the exception that host name resolving will not work.

Thismeans that you should use IP addresses instead of hostnames

when specifyingMySQLprivileges!

InstallingMySQLsystem tables...

Fillinghelp tables...

Tostart mysqld at boot time you have to copy

support-files/mysql.server to the right placeforyour system

PLEASE REMEMBER TO SET A PASSWORD FOR THEMySQLroot USER!

Todoso,start the server,thenissue the following commands:

/usr/bin/mysqladmin-u root password'new-password'

/usr/bin/mysqladmin-u root-h slave password'new-password'

Alternativelyyou can run:

/usr/bin/mysql\_secure\_installation

which will also give you the option of removing the test

databases and anonymous user created by default.Thisis

strongly recommendedforproduction servers.

Seethe manualformore instructions.

Youcan start theMySQLdaemon with:

cd/usr;/usr/bin/mysqld\_safe&

Youcan test theMySQLdaemon with mysql-test-run.pl

cd/usr/mysql-test;perl mysql-test-run.pl

Pleasereport any problems with the/usr/bin/mysqlbug script!

\[OK\]

Startingmysqld:\[OK\]

修改密码登陆

\[root@master~\]\#mysqladmin-u root password'123456'

\[root@master~\]\#mysql-uroot-p123456

Welcometo theMySQLmonitor.Commandsendwith;or\\g.

YourMySQLconnection idis3

Serverversion:5.1.73Sourcedistribution

Oracleisa registered trademark ofOracleCorporationand/orits

affiliates.Othernames may be trademarks of their respective

owners.

Type'help;'or'\\h'forhelp.Type'\\c'to clear the current input statement.

mysql>

\[root@slave~\]\#mysqladmin-u root password'123456'

\[root@slave~\]\#mysql-uroot-p123456

Welcometo theMySQLmonitor.Commandsendwith;or\\g.

YourMySQLconnection idis3

Serverversion:5.1.73Sourcedistribution

Oracleisa registered trademark ofOracleCorporationand/orits

affiliates.Othernames may be trademarks of their respective

owners.

Type'help;'or'\\h'forhelp.Type'\\c'to clear the current input statement.

mysql>

主数据库

创建测试数据库

mysql>create database HA;

QueryOK,1row affected(0.00sec)

mysql>useHA;

Databasechanged

mysql>

mysql>create table T1(idint,name varchar(20));

QueryOK,0rows affected(0.00sec)

mysql>insertintoT1 values(1,'a'),(2,'b');

QueryOK,2rows affected(0.00sec)

Records:2Duplicates:0Warnings:0

停数据库

\[root@master~\]\#service mysqld stop

Stoppingmysqld:\[OK\]

修改配置文件

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

\[mysqld\]

log-bin=mysql-bin-master

server-id=1

启动数据库

\[root@master~\]\#service mysqld start

Startingmysqld:\[OK\]

授权从服务器访问刷新权限

\[root@master~\]\#mysql-uroot-p123456

mysql>grant replication slave on\*.\*to slave@128.0.0.102identifiedby'123456';

QueryOK,0rows affected(0.00sec)

mysql>flush privileges;

QueryOK,0rows affected(0.00sec)

查看master信息

mysql>show master status \\G

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

File:mysql-bin-master.000001

Position:330

Binlog\_Do\_DB:

Binlog\_Ignore\_DB:

1rowinset(0.00sec)

备份需要同步的数据库

\[root@master~\]\#mysqldump-uroot-p123456 HA>HA.sql

复制主数据到从服务器上

\[root@master~\]\#scp HA.sql root@128.0.0.102:/root/

查看事件

mysql>show binlog events;

\+-------------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------+

\+-------------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------+

|mysql-bin-master.000001|4|Format\_desc|1|106|Serverver:5.1.73-log,Binlogver:4|

|mysql-bin-master.000001|106|Query|1|255|grant replication slave on\*.\*to slave@128.0.0.102identifiedby'123456'|

|mysql-bin-master.000001|255|Query|1|330|flush privileges|

\+-------------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------+

3rowsinset(0.00sec)

从数据库

从数据库创建数据库

mysql>create database HA;

QueryOK,1row affected(0.00sec)

导入数据库

\[root@slave~\]\#mysql-uroot-p123456 HA

测试数据库连通性

\[root@slave~\]\#mysql-uslave-p123456-h128.0.0.101

Welcometo theMySQLmonitor.Commandsendwith;or\\g.

YourMySQLconnection idis5

Serverversion:5.1.73-logSourcedistribution

Oracleisa registered trademark ofOracleCorporationand/orits

affiliates.Othernames may be trademarks of their respective

owners.

Type'help;'or'\\h'forhelp.Type'\\c'to clear the current input statement.

关掉服务

\[root@slave~\]\#service mysqld stop

Stoppingmysqld:\[OK\]

修改配置文件

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

\[mysqld\]

server-id=2

启动从

\[root@slave~\]\#service mysqld start

Startingmysqld:\[OK\]

配置

sql>change master to master\_host='128.0.0.101',master\_user='slave',master\_password='123456';

QueryOK,0rows affected(0.02sec)

mysql>start slave;

QueryOK,0rows affected(0.00sec)

启动从服务

mysql>start slave;

QueryOK,0rows affected(0.00sec)

查看从状态

mysql>start slave;

QueryOK,0rows affected(0.00sec)

mysql>show slave status \\G

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

Slave\_IO\_State:Waitingformaster to sendevent

Master\_Host:128.0.0.101

Master\_User:slave

Master\_Port:3306

Connect\_Retry:60

Master\_Log\_File:mysql-bin-master.000001

Read\_Master\_Log\_Pos:330

Relay\_Log\_File:mysqld-relay-bin.000002

Relay\_Log\_Pos:482

Relay\_Master\_Log\_File:mysql-bin-master.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:330

Relay\_Log\_Space:638

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:

1rowinset(0.00sec)

到此配置完毕

希望大家能交流技术，有问题一起研究，指出我的不足，让我进步

简单配置mysql主从_mysql主从简单配置

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 mysql主从配置

相关 mysql主从库配置_mysql主从库配置

相关简单配置mysql主从_mysql主从简单配置

相关 mysql 主从之简单配置

相关 mysql 配置主从

相关 MySQL主从配置

相关 MySQL主从配置

相关 Mysql主从配置

相关简单配置MYSQL主从

相关 MySQL主从配置