Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Updated using 4PSA automated script

...

  1. Edit MySQL config file on the master server: /etc/my.cnf. At the end of the file, add the following lines. Then save changes and restart MySQL.

    Code Block
    languagetext
    titleSQL Master - /etc/my.cnf
    #replication
    expire_logs_days=30
    server-id = 1
    log-bin=/var/lib/mysql/mysql-bin.log
    log_bin_trust_function_creators=1
    binlog_format=MIXED


  2. Add the MySQL user destined for replication with the appropriate rights. Log in on MySQL CLI and run the following command:

    Code Block
    languagetext
    titleSQL Master - SQL console
    GRANT SELECT, PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD ON *.* TO replica@'%' identified by 'password'; FLUSH PRIVILEGES;


    Warning
    titlePassword change

    Replace the password string with a custom, complex password. You can generate one using http://passwordsgenerator.net/ or a similar site.


  3. Stop the MySQL service on the slave server:

    Code Block
    languagetext
    titleSQL Slave - command line
     service mysql stop


  4. Go back to the master node and run the following command. In order to synchronize the folders, you will have to provide the root password for connecting via SSH on the slave server.

    Code Block
    languagetext
    titleSQL Master - commadn line
    rsync -varPe ssh /var/lib/mysql root@<slave_ip>:/var/lib/ 


    Warning
    titleIP replacement

    Change the <slave_ip> with your SQL slave IP address.


  5. Lock the database to prevent any new changes.

    Code Block
    languagetext
    titleSQL Master - SQL console
    FLUSH TABLES WITH READ LOCK;


  6. Check status on master server:

    Code Block
    languagetext
    titleSQL Master - SQL console
    mysql> show master status;
    +------------------------------+----------+--------------+------------------+
    | File                         | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------------------+----------+--------------+------------------+
    | centos-6-b64-test-bin.000001 | 43311833 |              |                  |
    +------------------------------+----------+--------------+------------------+


  7. Again, synchronize the MySQL home directory on the slave server. Run the rsync command on the master server one more time.

    Code Block
    languagetext
    titleSQL Master - command line
    rsync -varPe ssh /var/lib/mysql root@<slave_ip>:/var/lib/ 


  8. Unlock tables.

    Code Block
    languagetext
    titleSQL Server - SQL console
    UNLOCK TABLES;
    QUIT;


  9. Edit MySQL config file on the slave server: /etc/my.cnf. At the end of the file, add the following lines:  

    Code Block
    languagetext
    titleSQL Slave - /etc/my.cnf
    expire_logs_days=30
    log_bin=/var/lib/mysql/mysql-bin.log
    server-id=2
    max_allowed_packet=128M
    binlog_format=MIXED
    log_bin_trust_function_creators=1
    


  10. Start MySQL service on the slave server, connect to MySQL CLI and run the following command:

    Code Block
    languagetext
    titleSQL Slave - SQL console
    stop slave;


  11.  Set the master parameters on the slave server.

    Code Block
    languagetext
    titleSQL Slave - SQL console
    change master to master_host='<master_ip>', master_user='replica', master_password='<password>', MASTER_LOG_FILE='<log_file>', MASTER_LOG_POS=<position>;


    Warning
    titleEnter correct configuration parameters

    Replace <master_ip> with your SQL master IP address.

    Replace <password> with the previously generated password.

    Replace <log_file> with the log file name displayed in step 6 ( centos-6-b64-test-bin.000001 in our example).

    Replace <position> with the position index displayed in step 6 ( 43311833 in our example).

    Then disable the partition update event and start the slave node:

    Code Block
    languagetext
    titleSQL slave - SQL console
    alter event partition_update disable;
    start slave;

     

  12. Check the slave status.

    Code Block
    languagetext
    titleSQL slave - SQL console
    mysql> show slave status\G;
    *************************** 1. row ***************************
    ..
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    ..
    Exec_Master_Log_Pos: 43315418
    ..

 


Info

A successful replication is indicated by Slave_IO_Running and Slave_SQL_Running being Yes. If the two parameters have different values, replication is not configured correctly.

...

Content by Label
spaceslabels
showLabelsfalse
max5com.atlassian.confluence.content.render.xhtml.model.resource.identifiers.SpaceResourceIdentifier@5d4a10c1
sortmodified
showSpacefalse
reversetrue
typepage
SQL replication MySQL PBX nodes master slave
cqllabel in ("slave","sql","replication","pbxmysql","mysqlnodes","nodespbx","master") and type = "page" and space = "4PSAKB"

...

hiddentrue

...

,"slave") and space = currentSpace()

Except where otherwise noted, content in this space is licensed under a Creative Commons Attribution 4.0 International.