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
Does the log_bin need to point to different files on both instances? (you'll see here, mine does not).
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)