0

I have been trying to setup a mater-slave replication on the same machine using mysqld_multi and it does appear to be working, but I have a few questions which have never really been an issue before when running on different machines, so, if I first include both my cnf files for the master and slave respectively;

for the master;

[mysqld1]

user            = mysql
pid-file        = /var/run/mysqld/mysqld_master.pid
socket          = /var/run/mysqld/mysqld_master.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql_master
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

bind-address            = 127.0.0.1    

key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam_recover_options  = BACKUP

query_cache_limit       = 1M
query_cache_size        = 16M

log_error = /var/log/mysql/error_master.log

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size   = 100M
#binlog_do_db           = include_database_name
innodb_flush_log_at_trx_commit  = 1
sync_binlog                 = 1
binlog-format               = ROW

character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

and for the slave;

[mysqld2]

user            = mysql
pid-file        = /var/run/mysqld/mysqld_slave.pid
socket          = /var/run/mysqld/mysqld_slave.sock
port            = 3307
basedir         = /usr
datadir         = /var/lib/mysql_slave
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

bind-address            = 127.0.0.1

key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam_recover_options  = BACKUP

query_cache_limit       = 1M
query_cache_size        = 16M

log_error = /var/log/mysql/error_slave.log
server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size   = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = exclude_database_name
relay-log               = /var/log/mysql/relay-bin
#relay-log-index                = /var/log/mysql/relay-bin.index
#master-info-file       = /var/log/mysql/master.info
#relay-log-info-file    = /var/log/mysql/relay-log.info

character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci
  1. Does the log_bin need to point to different files on both instances? (you'll see here, mine does not).

  2. What does the following do (from the master);

    innodb_flush_log_at_trx_commit = 1

    sync_binlog = 1

    binlog-format = ROW

Below is a SHOW SLAVE STATUS\G;

MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000016
          Read_Master_Log_Pos: 780
               Relay_Log_File: relay-bin.000004
                Relay_Log_Pos: 756
        Relay_Master_Log_File: mysql-bin.000016
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 780
              Relay_Log_Space: 1048
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative
1 row in set (0.00 sec)
1

No. Keep everything separate, including my.cnf, log files, port, socket, data, etc. Be sure to have a different server_id for each.

Better yet, put them in different VMs (or use Docker). There will still be things that you must be sure to keep separate: data, port numbers, and possibly other things.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.