• After installation mysql database is not a master database because:
    • “server-id” and “log_bin” in /etc/mysql/my.cnf are commented out
    • it is also bind only to local host so connections from outside are not possible
    • it has by default allowed root access only from localhost
    • you have to set your own root password using following command (“password” is a key word not “yourpassword”)
      mysqladmin -u root password
      • you will be asked for password
  • To change it edit /etc/mysql/my.cnf and
    • Comment out line “bind-address = 127.0.0.1” to allow access from any IP or to set it to some IP you want to use for connection.
    • Remove comment character # in front of “server-id” and set it to unique number and really check that it is not used in any other of your mysql instances!!!
    • Remove comment character # in front of “log_bin” – default path “/var/log/mysql/mysql-bin.log” is OK unless you have a very good reason to change it – for example if you need to place binary logs on different filesystem etc.
    • check variable “max_binlog_size”, default is 100M and it served us OK all the time
    • There is also variable “binlog_do_db” which people usually set – BUT there is gotcha in this setting !!!
    • It is also necessary to ensure that binary logs will be available on master for some time – set “expire_logs_days” to some reasonable value depending on available disk space
    • If you run on cloud it could be useful to stop using DNS (there can be errors in decoding IP and hostnames):
      • skip-host-cache
      • skip-name-resolve
    • IMPORTANT: if machine was previously SLAVE, you should add:
      • skip-slave-start=TRUE
      • log-slave-updates = ON
    • if you already experienced some network issues regarding your databases it is a good idea to set higher limit for connect errors:
      • max_connect_errors=30
    • in case of network problems also compression of logs can help (must be set on both master and replica)
      • slave_compressed_protocol=ON
  • After changes in config file restart mysql service “sudo service mysql restart”
  • After restart check status of master:
    show master status;
    • you should see output like this (numbers will be different):
      +------------------+----------+--------------+------------------+
      | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
      +------------------+----------+--------------+------------------+
      | mysql-bin.000597 |   297908 | dbname       |                  |
      +------------------+----------+--------------+------------------+
      1 row in set (0.00 sec)
  • To allow connection of remote users from other IPs you must also GRANT proper rights on database – like this:
    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
    FLUSH PRIVILEGES;
    
  • You must also GRANT replication rights to proper user:
    GRANT REPLICATION SLAVE ON *.* TO 'replicationuser'@'%' IDENTIFIED BY 'password';
    FLUSH PRIVILEGES;
    
  • Only now is your database suitable for use as master database