简单配置mysql主从_mysql主从简单配置
环境
hostname
ip
端口
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
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…
OK
Fillinghelp tables…
OK
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
InitializingMySQLdatabaseThehost’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…
OK
Fillinghelp tables…
OK
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
Copyright(c)2000,2013,Oracleand/orits affiliates.Allrights reserved.
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
Copyright(c)2000,2013,Oracleand/orits affiliates.Allrights reserved.
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
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;
+————————————-+——-+——————-+—————-+——————-+——————————————————————————————————————+
|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|
+————————————-+——-+——————-+—————-+——————-+——————————————————————————————————————+
|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
Copyright(c)2000,2013,Oracleand/orits affiliates.Allrights reserved.
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)
到此配置完毕
希望大家能交流技术，有问题一起研究，指出我的不足，让我进步
﹏ヽ暗。殇╰゛Y
红太狼
超、凢脫俗
还没有评论，来说两句吧...