Search code examples
mysqldatabase-replicationshared-hosting

Replicate mysql database without root access to my.conf file?

I'm trying to find a way to replicate data from a remote shared server, where i don't have root access, to a local server, to maintain a offline access. Is there any option to do it? I'm using innodb storage engine.

Solution

  • You need root access to enable binary logging if it's not already enabled. You can see if it's enabled by running SHOW GLOBAL VARIABLES LIKE 'log_bin' for example.

    +---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

    Enabling binary logs also requires a restart of the MySQL server. Sorry, there's no way around that.

    Once binary logging is enabled on the master, you need:

    • A recent data dump from the master server, with the binary log coordinates (e.g. created with mysqldump --master-data). You can get this data dump without root access, but you just need a few privileges on all tables in all databases. Alternatively, an administrator could create the data dump for you.
    • A MySQL user on the master that has the REPLICATION SLAVE privilege, and allows connections from your local instance.

    Restore the data dump to your local MySQL instance, and use CHANGE MASTER to configure replication from the master, using the replica user and the binlog coordinates associated with the data dump. Then START SLAVE.

    More detailed instructions on configuring replication are found here: http://dev.mysql.com/doc/refman/5.7/en/replication-howto.html