mysql5.5 Master-Slave configuration

Source: Internet
Author: User
Tags sql error

 MySQL master-Slave synchronization # One: MySQL database master and slave

There is a discrepancy between MySQL database 5.5 and the previous version of the 5.5 database, which is for configuration after database 5.5.

1. Main library edit my.cnf (Linux my.cnf under/ETC/MY.CNF path):

"Mysqld"
#注意下面的配置是要在配置文件的mysqld段进行配置, the wrong position may cause MySQL to fail to start. A lot of MySQL does not start when the MY.CNF configuration error is usually caused by

#名字随意取名, the meaning of this configuration is to open the binary log function of MySQL, because the MySQL database master and slave is read from the database of the main database binary log to achieve.

#当然这个参数不一定要在这里配置, you can also specify the-log-bin parameter when MySQL starts.

Log-bin=new_name
Server-id=1 #配一个唯一的ID编号, 1 to 32.
#设置要进行或不要进行主从复制的数据库名 and set it up from the database as well.
binlog-do-db= Database name 1
binlog-do-db= Database Name 2
binlog-ignore-db= Database name 1
binlog-ignore-db= Database Name 2

#on *. * Must be *. *, not the specified database, or SQL error, because the master and slave is actually in the configuration file indicate which database needs to be synchronized, which database does not need synchronization
#给从数据库机器建立一个能访问主数据库的用户 (Of course the user is built on the primary database)
mysql> grant replication Slave on * * to [e-mail protected] identified by ' 123456 ';
#刷新权限
mysql> flush Privileges;
#查看主库情况, the following results show that the configuration was successful
Mysql> Show master status;
------------------------------------------------------+
File Position binlog_do_db binlog_ignore_db

------------------------------------------------------+
mysql-bin.000008 337

------------------------------------------------------+
#在从数据库操作时候用得到文件名和位置, the file name is the binary file that reads the primary database to synchronize, starting at which point in the binary
Record the binary log file name and location

2. Edit the my.cnf from the library and change the Server-id to the same number as the host.
If you want to synchronize only the specified database, you can add replicate-do-db = yourdatabase,
Synchronous a few copies of a few, the database name to change the good.
"Mysqld"
server-id=2 #唯一
#设置要进行或不要进行主从复制的数据库名, and also set on master.
replicate-do-db= Database name 1
replicate-do-db= Database Name 2
replicate-ignore-db= Database name 1
replicate-ignore-db= Database Name 2

#这个mysql指令里有文件名和位置的指明
mysql> Change Master to master_host= ' 192.168.1.100 ', master_user= ' slaveuser ', master_password= ' 123456 ', Master_log _file= ' mysql-bin.000008 ', master_log_pos=337;
#开始同步线程
mysql> start slave;
#查看开启情况
Mysql> Show slave status;
If it appears:
Slave_io_running:yes
Slave_sql_running:yes
Indicates master-slave synchronization in progress

Two: master database or MySQL master-slave synchronization in the case of the database hangs off
1, when the main database hangs, in fact, by default from the database every 60 seconds to access the main database to synchronize, until the database is opened
2, when hanging from the database, directly using the command start slave start synchronization function can be

Three: MySQL master-slave synchronization and operating system-independent, and the MySQL version is basically irrelevant.

Four: MySQL master-slave synchronization speed is basically the same as within seconds, but of course there is a difference in time, so from the database basically only do query,
In the modification, the real-time requirement is high, the operation of things within the unit, not from the library, with the main library link.

Five: The corresponding system when accessing from the database, basically only to configure it to read the database users, it is recommended that read-only database users in the following way:
GRANT SELECT on yry360.* to [e-mail protected] identified by "XXXX";

mysql5.5 Master-Slave configuration

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.