A double-click Hot standby for MySQL database setup in Linux environment

Source: Internet
Author: User
Tags mysql version one table sql error unique id variable scope

  • Preparing the server
  • Because the Binlog format may not be the same in different versions of MySQL (binary log), the best combination is the MySQL version of the primary (master) server and the same or lower from the (Slave) server version, and the master server version must not be higher than the from server version.
    The two server versions I used to test are Mysql-5.5.17.

      1. Mysql Build master-Slave server dual-machine hot standby configuration steps

    2.1 Environmental Description
    A server (master server Master): 59.151.15.36
    b Server (from server slave): 218.206.70.146
    MySQL version of master-slave server is 5.5.17
    The Linux environment
    A copy of the database content that the primary server needs to synchronize is uploaded to the slave server to ensure that the database content in both servers is consistent at the beginning.
    However, as explained here, because I am using MySQL after the installation of the database test, so there is no table in the two servers, only the test is not built in the same empty table tb_mobile;
    The SQL statements are as follows:
    Mysql> CREATE TABLE Tb_mobile (mobile VARCHAR comment ' mobile phone number ', Time timestamp DEFAULT now () comment ' Times ');
    2.2 Primary Server Master configuration
    2.2.1 Creating a synchronization user
    Enter the MySQL operator interface to establish a connection account on the primary server for the slave server, which must grant replication slave permissions. Since MySQL version 3.2 can be used by the replication to the dual-machine hot standby function operation.
    The operation instructions are as follows:
    mysql> Grant replication Slave on.To ' replicate ' @ ' 218.206.70.146 ' identified by ' 123456 ';
    mysql> flush Privileges;
    Once the synchronization connection account has been created, we can see if the connection is successful by accessing the primary server (master) database with the Replicat account on the slave server (Slave).
    Enter the following command on the slave server (Slave):
    [Email protected] ~]# mysql-h59.151.15.36-ureplicate-p123456
    If the following results appear, you can log on successfully, indicating that both servers are ready to operate on a two-machine hot standby.

    2.2.2 Modifying the MySQL configuration file
    If the above preparations are done, we can make changes to the MySQL configuration file, first find the MySQL configuration all in the directory, generally after the installation of the MySQL service, the configuration file will be copied one by one copies out into the/ect directory, and the configuration file named: my.cnf. That is, the exact configuration file directory is/ETC/MY.CNF
    (MySQL installed under Linux with RPM package will not install the/etc/my.cnf file,
    As for why there is no such file and MySQL can start and function normally, at the point there are two statements,
    The first argument is that MY.CNF is just a parameter file at MySQL startup, and without it, MySQL will start with the built-in default parameters,
    The second argument is that MySQL automatically uses the my-medium.cnf file in the/usr/share/mysql directory at startup, which is limited to the RPM package installation of MySQL,
    Workaround, just copy the my-medium.cnf file from the/usr/share/mysql directory to the/etc directory and rename it to MY.CNF. )
    After locating the profile my.cnf open, modify it under [mysqld]:
    [Mysqld]
    Server-id = 1//Unique ID
    Log-bin=mysql-bin//One of these two lines is the original, you can not move, add the following two lines. Specifying log files
    BINLOG-DO-DB = test//Log database
    binlog-ignore-db = mysql//log-not logged database
    2.2.3 Restart MySQL Service
    After modifying the configuration file, after saving, restart the MySQL service, if successful, no problem.

    2.2.4 Viewing the primary server status
    After entering the MySQL service, the command can be used to view the master status and enter the following command:

    Note the parameters inside, especially the first two file and position, which can be useful to configure the master-slave relationship from the server (Slave).
    Note: The lock table is used here, in order to generate the environment in the new data, so that from the server location synchronization location, the initial synchronization is completed, remember to unlock.

    2.3 Configuration from server slave
    2.3.1 Modifying a configuration file
    Because this is in the main-from the way to achieve the MySQL dual-machine hot standby, so in the slave server is not in the establishment of synchronization account, directly open the configuration file my.cnf to modify, the same reason is the same as the modification of the main server, just need to modify the parameters are different. As follows:
    [Mysqld]
    Server-id = 2
    Log-bin=mysql-bin
    REPLICATE-DO-DB = Test
    Replicate-ignore-db = Mysql,information_schema,performance_schema
    2.3.2 Restart MySQL Service
    After modifying the configuration file, after saving, restart the MySQL service, if successful, no problem.

    2.3.3 specifying the synchronization location with the change Mster statement
    This step is the most critical step, after entering the MySQL operator interface, enter the following command:
    Mysql>stop slave; It is important to stop the slave service thread first, and if you do not do this it will cause the following operations to be unsuccessful.
    Mysql>change Master to

    master_host= ' 59.151.15.36 ', master_user= ' replicate ', master_password= ' 123456 ',
    master_log_file= ' Mysql-bin.000016 ', master_log_pos=107;
    Note: master_log_file, Master_log_pos is determined by the status value identified by the master server (master). It's just called attention. Master_log_file corresponds to file, Master_log_pos corresponds to position. Mysql 5.x or later does not support the option to specify the primary server in the configuration file.
    problems encountered, if you follow the above steps, the following occurs:

    to reset the slave. The instruction is as follows
    Mysql>stop slave;
    Mysql>reset slave; The
    stops the slave thread from starting again. After success, you can turn on the slave thread.
    Mysql>start Slave;
    2.3.4 View Slave server (Slave) Status
    View
    mysql> show Slave status\g with the following instructions:

    Viewing the following two key values is yes, which means that the setting is successful from the server.
    Slave_io_running:yes
    Slave_sql_running:yes
    2.4 Test synchronization
    has been said before in the database test that there is only one table Tb_mobile no data, We can check whether the database of the next two servers has data:
    master:59.151.15.36

    slave:218.206.70.146

    OK, now you can insert data in the Master server to see if it can be synchronized.
    master:59.151.15.36

    slave:218.206.70.146

    can be seen from the above two, the data inserted on the Master server can be found on the Slave server, This means that the dual-machine hot standby configuration is successful.

      1. Mysql Establish Master-master server hot standby configuration step
        Server or use back the two servers
        3.1 create a synchronization user
        at the same time establish a connection account in the master and slave server, the account must be granted repliation Slave permissions. This is because server A and Server B are mainly from each other, so we have to set up a synchronization user separately.
        Server A:
        mysql> grant replication Slave on . to ' replicate ' @ ' 218.206.70.146 ' identified by ' 123456 ';
        mysql> flush Privileges;
        Server B:
        mysql> grant replication Slave on . to ' replicate ' @ ' 59.151.15.36 ' identified by ' 123456 ';
        mysql> flush Privileges;
        3.2 Modify configuration file my.cnf
        Server A
        [mysqld]
        Server-id = 1
        log-bin=mysql-bin
        binlog-do-db = Test
        binlog-ignore-db = MySQL
        #主-the main form needs to add more parts
        log-slave-updates
        Sync_binlog = 1
        Auto_increment_offset = 1
        Auto_increment_increment = 2
        replicate-do-db = Test
        Replicate-ignore-db = Mysql,information_schema
        Server B:
        [mysqld]
        Server-id = 2
        log-bin=mysql-bin
        replicate-do-db = Test
        Replicate-ignore-db = MySQL, Information_schema,performance_schema

    #主-The main form needs to add more parts
    BINLOG-DO-DB = Test
    binlog-ignore-db = MySQL
    Log-slave-updates
    Sync_binlog = 1
    Auto_increment_offset = 2
    Auto_increment_increment = 2
    3.3 Restart the MySQL service on Server A and Server B, respectively
    Restart the server the same way as above, there is no explanation.
    3.4 Check the status of a server and B server as the primary server respectively
    Server A:

    Server B:

    3.5 Specify the synchronization location on the a server and the B server, respectively, with change master
    Server A:
    Mysql>change Master to

    Master_host= ' 218.206.70.146 ', master_user= ' replicate ', master_password= ' 123456 ',
    Master_log_file= ' mysql-bin.000011 ', master_log_pos=497;
    Server B:
    Mysql>change Master to
    Master_host= ' 59.151.15.36 ', master_user= ' replicate ', master_password= ' 123456 ',
    Master_log_file= ' mysql-bin.000016 ', master_log_pos=107;
    3.6 Restart from the service thread on a and B servers respectively
    Mysql>start slave;
    3.7 Viewing from server status on a and B servers, respectively
    Mysql>show slave status\g;
    Viewing the following two key values are yes, which means that the setting is successful from the server.
    Slave_io_running:yes
    Slave_sql_running:yes
    3.8 Test Master-Master Synchronization example
    Test Server A:
    Insert a statement on Server A as shown in:


    Then check on server B to see if the synchronization is as shown:


    Test Server B:
    Insert a statement on Server B as shown in:

    Then, from server A, see if there is synchronization data as shown in:



    Finally, it can be seen from the results that the master-master form of dual-machine hot standby can be successfully realized.

    1. Configuration parameter Description
      Server-id
      The ID value uniquely identifies the master and slave servers in the replication cluster, so they must be different. The master_id must be a positive integer value between 1 and 232-1, and the slave_id value must be a positive whole number between 2 and 232-1.
      Log-bin
      Indicates that opening binlog, open this option can be written to slave relay-log through I/O, is also a prerequisite to replication.
      Binlog-do-db
      Represents a database that requires logging of binary logs. If more than one data can be separated by commas, or multiple BINLOG-DO-DG options are used.
      Binglog-ingore-db
      Represents a database that does not require logging of binary logs, if multiple databases can be separated by commas, or the multi-binglog-ignore-db option is used.
      Replicate-do-db
      Represents a database that needs to be synchronized, if multiple data can be separated by commas, or multiple replicate-do-db options are used.
      Replicate-ignore-db
      Represents a database that does not need to be synchronized, if more than one database can be separated by commas, or multiple replicate-ignore-db options are used.
      Master-connect-retry
      Master-connect-retry=n indicates that the connection from the server to the primary server is not successful, and then waits n seconds (s) before it is managed (the default setting is 60s). If the Mater.info file exists from the server, it ignores the options.
      Log-slave-updates
      Configure whether the update operation from the library is written to the binaries, and if this is done from the library and other main libraries from the library, then this parameter needs to be hit so that the log can be synchronized from the library from the library.
      Slave-skip-errors
      During the copy process, SQL error in Binglo due to various reasons, by default, the copy is stopped from the library and the user is involved. You can set Slave-skip-errors to define the error number, and you can pass if the error encountered during the copy process is a defined error number. If the from library is used for backup, setting this parameter will present inconsistent data, do not use. If you are sharing the query pressure of the main library, consider it.
      --slave-skip-errors=[err_code1,err_code2,... | All|ddl_exist_errors]
      Command-Line Format--slave-skip-errors=name
      Option-file Format slave-skip-errors
      System Variable Name slave_skip_errors
      Variable Scope Global
      Dynamic Variable No
      Permitted Values
      Type string
      Default OFF
      Valid Values OFF
      [List of error codes]
      All
      Ddl_exist_errors

    MySQL 5.6 as well as MySQL Cluster NDB 7.3 Support an additional shorthand value
    Ddl_exist_errors, which is equivalent to the error code list 1007,1008,1050,1051,
    1054,1060,1061,1068,1094,1146.
    Examples:

    --slave-skip-errors=1062,1053
    --slave-skip-errors=all
    --slave-skip-errors=ddl_exist_errors
    Sync_binlog=1 Or N
    The default value for Sync_binlog is 0, and in this mode, MySQL is not synced to disk. In this case, MySQL relies on the operating system to flush binary log binaries, just as the operating system refreshes other files. So if the operating system or machine (not just the MySQL server) crashes, it is possible that the last statement in the Binlog is lost. To prevent this, you can use the Sync_binlog global variable to synchronize the Binlog with the hard disk after every n binlog write. When the Sync_binlog variable is set to 1 is the safest, because in the case of crash crashes, your binary log binary logs can only lose up to one statement or one transaction. However, this is also the slowest way (unless the disk has cache caches with battery backup power, which makes it very fast to sync to disk).
    Even if Sync_binlog is set to 1, there is a possibility of inconsistency between the table content and the Binlog content when a crash occurs. If you use the InnoDB table, the MySQL server processes the commit statement, which writes the entire transaction to Binlog and commits the transaction to InnoDB. If a crash occurs between two operations, the transaction is InnoDB rolled back, but still exists in Binlog. You can use the-innodb-safe-binlog option to increase the consistency between InnoDB table content and Binlog. (Note:-innodb-safe-binlog is not required in MySQL version 5.1; This option is obsolete because of the introduction of XA transaction support), which provides greater security for Binlog (sync_binlog=1) per transaction and (the default is True) the InnoDB log is synchronized with the hard disk, and the effect of this option is that after the crash restarts, after the transaction is rolled back, the MySQL server cuts the rollback from the Binlog innodb transaction. This ensures that the Binlog feedback innodb the exact data of the table, etc., and keeps the slave server in sync with the primary server (without taking the rollback statement).
    Auto_increment_offset and Auto_increment_increment
    Auto_increment_increment and Auto_increment_offset are used for primary-primary server (Master-to-master) replication and can be used to control the operation of the Auto_increment column. All two variables can be set to global or local variables, and each value can be assumed to be an integer value between 1 and 65,535. Setting one of the variables to 0 causes the variable to be 1.
    These two variables affect the way auto_increment columns: auto_increment_increment The increment value of the value in the control column, Auto_increment_offset determines the starting point of the Auto_increment column value.
    If the value of Auto_increment_offset is greater than the value of auto_increment_increment, the value of Auto_increment_offset is ignored. For example, if there is some data in the table, it will be used as the initial value of the largest self-increment currently available.

    How to troubleshoot MySQL master-slave synchronization errors in SQL
    Solve:
    Stop slave;
    #表示跳过一步错误, the following numbers are variable
    Set global sql_slave_skip_counter = 1;
    Start slave;
    Then use mysql> Show slave status\g to view:
    Slave_io_running:yes
    Slave_sql_running:yes
    OK, now the master-slave synchronization state is normal.

    A double-click Hot standby for MySQL database setup in Linux environment

    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.