MySQL Database Replication

Questions regaring MySQL and PHP may go here
Forum rules
1) This is a user forum for Synology users to share experience/help out each other: if you need direct assistance from the Synology technical support team, please use the following form:

https://account.synology.com/support/support_form.php?lang=enu



2) To avoid putting users' DiskStation at risk, please don't paste links to any patches provided by our Support team as we will systematically remove them. Our Support team will provide the correct patch for your DiskStation model.
edwardso
Rookie
Rookie
Posts: 31
Joined: Fri Jun 01, 2007 11:59 am
Location: http://edwardso.dyndns.org
Contact:

MySQL Database Replication

Postby edwardso » Thu Nov 12, 2009 10:29 am

Did anyone try modify synology mysql to enable Database Replication ?
http://edwardso.dyndns.org/bo-blog
DS-107 / DSM 3.1-1605 / 2TB
DS-209j / DSM 3.1-1605 / 2 x 1TB (ST31000528AS + WDC WD10EADS-00L)
RS-407 / DSM 3.1-1605 / 4 x 500GB
RS-408RP / DSM 3.1-1605 / 4 x 2TB (WD20EARS-00MVWB0)
RS-409RP+ / DSM 3.1-1605 / 4 x 2TB (ST32000542AS WD20EARS-00MVWB0)
RS-810RP+ / DSM 3.1-1605 / 4 x 3TB (HDS723030ALA640)
User avatar
pomprocker
Knowledgeable
Knowledgeable
Posts: 389
Joined: Tue Jun 02, 2009 5:28 pm
Location: Mission Viejo, CA

Re: MySQL Database Replication

Postby pomprocker » Thu Nov 12, 2009 5:59 pm

what do you mean by database replication?
DS209
DSM4.2

http://pomprocker.blogspot.com
Nerd Stuff - Programming, Electronics, Mechanics
edwardso
Rookie
Rookie
Posts: 31
Joined: Fri Jun 01, 2007 11:59 am
Location: http://edwardso.dyndns.org
Contact:

Re: MySQL Database Replication

Postby edwardso » Thu Nov 12, 2009 6:59 pm

I mean :
MySQL replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync. This is not a backup policy because an accidentally issued DELETE command will also be carried out on the slave; but replication can help protect against hardware failures though.

How To Set Up Database Replication In MySQL
http://www.howtoforge.com/mysql_database_replication
http://edwardso.dyndns.org/bo-blog
DS-107 / DSM 3.1-1605 / 2TB
DS-209j / DSM 3.1-1605 / 2 x 1TB (ST31000528AS + WDC WD10EADS-00L)
RS-407 / DSM 3.1-1605 / 4 x 500GB
RS-408RP / DSM 3.1-1605 / 4 x 2TB (WD20EARS-00MVWB0)
RS-409RP+ / DSM 3.1-1605 / 4 x 2TB (ST32000542AS WD20EARS-00MVWB0)
RS-810RP+ / DSM 3.1-1605 / 4 x 3TB (HDS723030ALA640)
User avatar
pomprocker
Knowledgeable
Knowledgeable
Posts: 389
Joined: Tue Jun 02, 2009 5:28 pm
Location: Mission Viejo, CA

Re: MySQL Database Replication

Postby pomprocker » Thu Nov 12, 2009 8:04 pm

No i havent tried it myself but looking through chapter 16:
http://dev.mysql.com/doc/refman/5.0/en/ ... howto.html

I'd say it's possible to do this.
DS209
DSM4.2

http://pomprocker.blogspot.com
Nerd Stuff - Programming, Electronics, Mechanics
al404
Rookie
Rookie
Posts: 35
Joined: Thu Sep 04, 2014 1:46 pm

Re: MySQL Database Replication

Postby al404 » Fri Sep 26, 2014 2:05 pm

i'm looking for same thing, i would like to set MySQL on DS214 as replication slave
is it possible?
i think i have to exit my.cnf but not even know where it is
symano
I'm New!
I'm New!
Posts: 2
Joined: Fri Dec 19, 2014 8:02 pm

Re: MySQL Database Replication

Postby symano » Fri Dec 19, 2014 8:13 pm

I tried replication between DS213+ and DS1515+ and it works.

DSM 5.1: my.cnf is located /etc/mysql/my.cnf
User avatar
bartowski
I'm New!
I'm New!
Posts: 1
Joined: Sun Feb 08, 2015 7:38 am

Re: MySQL Database Replication

Postby bartowski » Sun Feb 08, 2015 7:44 am

I use DS414j as master, DS415+ as slave, it works.
You could use phpmyadmin to config master/slave and modify my.cnf to setup bin log, server-id,etc.
Watch your back!
User avatar
gamov
Beginner
Beginner
Posts: 27
Joined: Fri Oct 11, 2013 3:42 am

Re: MySQL Database Replication

Postby gamov » Thu Apr 28, 2016 3:56 am

Hi guys,
Which package / how did you install mysql on your Synology. Via the MariaDB package?
-----------------------------------------
DS413 (4.5TB SHR) | Gigabit Ethernet | Popcorn Hour A-400 | iMac 27" Late-2009 | MacBookPro 17" Early-2011
nothrem
I'm New!
I'm New!
Posts: 3
Joined: Sun Feb 05, 2012 4:34 pm

Re: MySQL Database Replication

Postby nothrem » Fri Mar 03, 2017 5:52 pm

You can install MySQL on Synology by installing package "PhpMyAdmin" which will automatically install all other packages (PHP, MariaDB, WebServer, etc.).

UPDATE: now you can select either MariaDB 5 (MySQL 5.5), or MariaDB 10 (which replaces MySQL 5.6). MariaDB 5 runs on port 3306 (default MySQL port) and MariaDB 10 runs on 3307.

Note that all settings done in my.cfg will be reset after each update. If you want to change the server-id and/or port, I recommend disabling automatic update (in DSM) of the package!

To create Master-Slave replication, you can follow the official guide at https://dev.mysql.com/doc/refman/5.7/en/replication-howto.html with few exceptions (specific paths for Synology NAS, etc.):

For MariaDB 5:
  • The configuration of MariaDB5 my.cnf is stored in /volume1/@appstore/MariaDB/etc/mysql/my.cnf (if you installed the packages on Volume1).
  • To edit my.cfg via SSH you must use root access:

    Code: Select all

    > sudo vi /volume1/@appstore/MariaDB/etc/mysql/my.cnf

    The password is the one you use to login to DSM as admin.
    To edit file press "i" (insert mode), to save changes press ESC, then ":" (colon) and "wq" (write and quit) and confirm by Enter.
    Reboot whole NAS after you make any changes, because MariaDB package cannot be stopped alone (other packages depend on it).
  • Binary files of mysql are stored in cusr/bin/ (if you installed the packages on Volume1) so to import master backup use command:

    Code: Select all

    > /volume1/@appstore/MariaDB/usr/bin/mysql -uroot -pdbroot < /volume1/backup/master_dump.sql

    where dbroot is the password configured in MariaDB app in DSM and /volume1/backup/master_dump.sql is path to the master dump file (which you should upload to a folder on your slave NAS).
    Note that you don't need to use the --host nor --port parameter, because the mysql command is already modified to automatically connect to specific server and port (localhost:3306).
    Files should be also linked to /usr/bin so you can use command /usr/bin/mysql ...
    If the import throws an error, you can edit the SQL dump by command:

    Code: Select all

    > vi +123 /volume1/backup/master_dump.sql

    where 123 is the line where an invalid syntax or definition was reported. See the next step...
  • MariaDB 5 contains MySQL 5.5 which means it has some limitations. If your Master is MySQL 5.6+, you should use the MariaDB 10 package.
    If you want to import Mysql 5.6/MariaDB 10 master file into MariaDB5 anyway you may need to delete invalid definitions from your master dump, e.g. STATS_PERSISTENT=X (this is not needed on MySQL 5.5) or DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP (replication will work without these definitions, but timestamp columns on slave may not have their default values), etc.
  • PhpMyAdmin can be accessed at address http://your.nas.com/phpMyAdmin or http://192.168.0.123/phpMyAdmin (where 192.168.0.123 is IP address of your NAS).
    From there you can use commands like CHANGE MASTER TO ..., START SLAVE, SHOW SLAVE STATUS, etc.
  • On master you must create new user whose host (computer name) value will be the IP address of your slave NAS and will have only the right of REPLICATION SLAVE, then use this username and password in CHANGE MASTER TO ... for replication.
  • If replication does not work, you can verify connection by logging to SSH and using command:

    Code: Select all

    > /volume1/@appstore/MariaDB/usr/bin/mysql -uslave -pdbslave -h master.server.com

    where master.server.com is address of your master server and slave and dbslave are username and password of the user created on master for replication.

For MariaDB 10:
  • The configuration of MariaDB10 my.cnf is stored in /usr/local/mariadb10/etc/mysql/my.cnf.
  • To edit my.cfg via SSH you must use root access:

    Code: Select all

    > sudo vi /usr/local/mariadb10/etc/mysql/my.cnf

    The password is the one you use to login to DSM as admin.
    To edit file press "i" (insert mode), to save changes press ESC, then ":" (colon) and "wq" (write and quit) and confirm by Enter.
    Reboot whole NAS after you make any changes, because MariaDB package cannot be stopped alone (other packages depend on it).
  • Binary files of mysql are stored in /usr/local/mariadb10/bin/mysql so to import master backup use command:

    Code: Select all

    > /usr/local/mariadb10/bin/mysql -uroot -pdbroot < /volume1/backup/master_dump.sql

    where dbroot is the password configured in MariaDB app in DSM and /volume1/backup/master_dump.sql is path to the master dump file (which you should upload to a folder on your slave NAS).
    Note that you don't need to use the --host nor --port parameter, because the mysql command is already modified to automatically connect to specific server and port (localhost:3307).
    The files are also stored in /volume1/@appstore/MariaDB10/usr/local/mariadb10/bin where you can find other MariaDB 10-related files.
    This version should support all features of MySQL 5.6 and MariaDB 10 so there should be no problem with the import. Should an error occur, see the above steps for MariaDB 5.
  • PhpMyAdmin can be accessed at address http://your.nas.com/phpMyAdmin or http://192.168.0.123/phpMyAdmin (where 192.168.0.123 is IP address of your NAS).
    From there you can use commands like CHANGE MASTER TO ..., START SLAVE, SHOW SLAVE STATUS, etc.
  • On master you must create new user whose host (computer name) value will be the IP address of your slave NAS and will have only the right of REPLICATION SLAVE, then use this username and password in CHANGE MASTER TO ... for replication.
  • If replication does not work, you can verify connection by logging to SSH and using the mysql command. However remember that the MariaDB 10's command is modified to connect to port 3307 so to connect master server running on port 3306 you will need to use the (uppercased) -P3306 or --port=3306 parameter:

    Code: Select all

    > /usr/local/mariadb10/bin/mysql -uslave -pdbslave -h master.server.com --port=3306

    or

    Code: Select all

    > /usr/local/mariadb10/bin/mysql -uslave -pdbslave -h master.server.com -P3306

    where master.server.com is address of your master server and slave and dbslave are username and password of the user created on master for replication.

To move your slave from MariaDB 5 to MariaDB 10 without stopping Master do this:
  • Note that this will remove existing MariaDB 10 databases, do NOT use this if you already have important databases in MariaDB 10!
  • Also note that your new slave may still be missing some data if your master is MySQL 5.6+ (e.g. where DEFAULT CURRENT_TIMESTAMP is used for a column)! To get all missing data into MariaDB 10 slave, you will need to do a full master-to-slave dump import (see above) instead of just copying old slave.
  • Check MariaDB 5 and MariaDB 10 apps in DSM and note the volume their databases are stored on. Change the following commands if they are stored on a different volume than volume 1.
  • Now you need to remember what is the last position of the old slave:
    Connect to MariaDB 5 via PhpMyAdmin and run these commands (click on SQL button on the top):

    Code: Select all

    STOP SLAVE;
    SHOW SLAVE STATUS;

    (note: in console, use \G instead of ';' to get better readable response.)
    Write down or copy values for Relay_Master_Log_File and Exec_Master_Log_Pos which is the last executed command from Master.
  • Stop both MariaDB packages and all packages dependent on them (i.e. PhpMyAdmin and maybe other)
  • remove or backup all MariaDB 10 database /volume1/@database/mariadb10

    Code: Select all

    sudo mv /volume1/@database/mariadb10 /volume1/@database/mariadb10.old
  • Now you can copy folders and files from MariaDB 5 to MariaDB10 database (use both commands to copy folders and files):

    Code: Select all

    sudo cp -R /volume1/@database/mysql /volume1/@database/mariadb10/
    sudo cp -R /volume1/@database/mysql/* /volume1/@database/mariadb10/
  • Start MariaDB 10 and PhpMyAdmin packages and login to MariaDB 10 via PhpMyAdmin. Then run these commands:

    Code: Select all

    STOP SLAVE;
    RESET SLAVE;
    CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1234;

    In this command replace mysql-bin.000001 and 1234 with the Relay_Master_Log_File and Exec_Master_Log_Pos values you have copied from the MariaDB 5 earlier.
  • Now you can start the slave and check replication:

    Code: Select all

    START SLAVE

    Wait few seconds for slave to start and then:

    Code: Select all

    SHOW SLAVE STATUS;
  • If you get Yes under both Slave_IO_Running and Slave_SQL_Running and nothing in both Last_IO_Error and Last_SQL_Error then everything is running and you can uninstall the MariaDB 5 if you don't need it anymore. You can also check replication by adding anything into Master DB (e.g. create new empty table) and checking that Exec_Master_Log_Pos on Slave has increased (and that the change is also present in the slave DB).

Return to “MySQL/PHP Mods”

Who is online

Users browsing this forum: No registered users and 1 guest