Comments - Setting Up Replication

I'm running primary as 10.11.3-MariaDB-1+rpi1-log Raspbian 12 and secondary as 10.11.4-MariaDB-1deb12u1 Debian 12. Obviously version mismatch is an issue but I have a more basic issue.

The file /.my.cnf sets server_id=2 in the secondary. It seems that this value is picked correctly because SHOW GLOBAL VARIABLES reports server_id=2.

Yet, I get the following message in SHOW SLAVE STATUS\G Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MariaDB server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

The primary is implicitly using server_id=1. In my limited knowledge there should be no conflict but there (apart from knowing which .cnf file is being used). Therefore, should not be any mismatch.

I am assuming that the primary is working fine (server_id=1) but what/where changes are needed in the secondary server for replication to start? Thanks.

Regards.

I have used these instructions to setup 10 or so master slave replications successfully. I now want to do the same where the master is on-premise, and the slave is on an AWS EC2 RDP. The on-premise network is connected via a site-to-site VPN. Communications have been tested sucessfuly. Can I use the same instructions above? When I look for the my.cnf file on the AWS RDP slave I cannot find it? Also when I try to use the change master to command it says I have to have "super" rights. When I try to enable super rights AWS will not allow me.

Is there a different set of instructions I should use for replicating with AWS?

This article is mentioning "server_id", but it seams that the real property name is "server-id".

No. In the configuration file, the parameter name is "server_id".

The hyphen is used in the command-line arguments.

Now you need prevent any changes to the data while you view the binary log position. You'll use this to tell the slave at exactly which point it should start replicating from.

On the master, flush and lock all tables by running FLUSH TABLES WITH READ LOCK. Keep this session running - exiting it will release the lock. Get the current position in the binary log by running SHOW MASTER STATUS:

Don't forget to check and possibly set two variables:

show variables like 'wait_timeout';
show variables like 'interactive_timeout';
set variables interactive_timeout=XXX;
set variables work_timeout=XXX;

The defaults for these are very large, at 28,800 seconds, but many installations set them smaller. Mine were 300 seconds, so my locks timed out and I had to do the database dump again.

I know that my dump takes 42 minutes, so I set them for 3600 seconds, or one hour. Your mileage may vary :)

I am a total database replication newbie with a dumb question: If I'm setting up a slave to which a database will be replicated from the master, why do I first have to backup the database from the master and restore it to the slave? Isn't that the very definition of "replication"? Why doesn't the replication process handle the copying?

You actually can use a "blank" slave and set it up to replicate every event that has happened on the master since the was blank too. Although technically possible, this would require that you 1) can identify the exact transaction on the master that was equivalent to the clean slave's current state, 2) have saved all the binary logs on the master from that blank state up to the present, and 3) have the time to replay the master's transaction history on the slave.

So your intuition is correct. It's just that practically speaking, letting the slave replay the master's transaction history from installation forward is a lot more difficult and time-consuming than starting from a recent backup.

I have added a slave of version mariadb 10.2.14 to a master of version mysql 5.5.20. i have table with character set latin1 on master and same table with utf4mb4 on slave.

As per my knowledge replication between different character set is not allowed. But i am able to replicate. I got below conversion error. Last_Errno: 1677 Last_Error: Column 0 of table 'xx.xx' cannot be converted from type 'varchar(100)' to type 'varchar(100)'

but when i used slave_type_conversions=All_NON_lossy on slave, everything is working fine. Using mixed binlog format .

Will you please help how replication is possible with different char set and how my error got resolve when setting 'slave_type_conversions' is only effective in case of row based logging?

I guess the safest way to go is to upgrade your master server to 10.2. I wonder if replication between 5.5 and 10.2 actually works, you say it does, but you face a problem that could arise from the version difference.

Thanks for the response. We are planning to upgrade same as you suggested, But i am still wondering how option "slave_type_conversions=All_NON_lossy" worked, when i am using mixed binary logging. Replication broken in case of truncate table which generated a statement type binary log.Please help in understanding the same.

After setting up a Galera cluster and realising is not exactly what I needed I decided to go for a master-slave setup. I set wserp_on=OFF and went through this guide.

As far as I know I did everything right but for some reason my slave gets Permission denied when connecting to the master. Thing is, if I connect manually from the slave to the master like: mysql -u master -p --host ip --port port then it connects without a problem. The user master is set with replication privileges. I am using MariaDB 10.1.21 on Centos 7.

Maybe it's a bug ?

Turns out semanage prevented the client machine from connecting remotely.

Firstly I notice neither here or in the Replication and Binary Log Server System Variables page there is mention of skip_slave_start. It is not in Server System Variables either. Could the documentation be amended and include some information on either the setting up page or elsewhere why to use or not use it in your server.cnf

It's an option rather than a variable, so listed at mysqld Options as well as Full List of MariaDB Options, System and Status Variables. It should be mentioned in the replication text though, so thanks for pointing that out, will incorporate it.

When using replication in MariaDB, slaves identify themselves at the master, and this identification is checked against valid users with replication permissions in the master database.

In my test situation this identification frequently fails. By checking the column Last_IO_Error in the 'SHOW ALL SLAVES STATUS' output I learned that the slave switched its identification between full computer name (computer name plus domain name) and IP address. Though these switches were not very frequent (maybe once per hour), they seemed to happen completely at random. As the user was created on the master with the full computer name as hostname, this resulted in a failure whenever the slave introduced itself by its IP address.

This problem might be easily circumvented by creating a user with a wildcard, or an IP mask along the pattern xxx.xxx.%.% for a hostname. But in the context where I am working, this is not an acceptable solution.

So my question is: what is the cause of this erratic behaviour, and is there a way to force the slave to use computername plus domain as its identification?

you may try add skip-name-resolve to your my.cnf, or start mysqld with the --skip-name-resolve option. this option would make mysql do not resolve host names when checking client connections (only use ip address).

But I want it the other way round! I have to use the full computer name for identification, IP-addresses are not allowed (and not practical as they may change). So skip-name-resolve would not help me.

Is it not simpler to setup replication using mysqldump with the --gtid and --master-data options? It seems to me that this would be much easier (and safer) for admins new to or experimenting with replication.

As a relative newbie to this myself I don't want to change the article myself, but it seems like a lot of this is just boiler-plate that mysqldump does for you while simultaneously generating a file that can be used to setup a slave in a single step.

"...and must be unique for each server in the replicating group." what is replicating group? all servers which take part into replication?

Not exactly. Imagine each group as a server, plus the servers (masters, slaves) that DIRECTLY communicate with the first server. Within such groups, id's must be unique.

The followind replication topography is ok:

id1 -> id:2 -> id:3 -> id:1

But of course it is a bad practice.

Grant:

GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'slave_host' IDENTIFIED BY 'bigs3cret'; FLUSH PRIVILEGES;

To check replication status:

SHOW SLAVE STATUS\G

Thanks for the quick howto.

MySQL replication is one of the features that we took into account when choosing the database for our application. It works really nice and can distribute the load when you need two kind of users (updaters and readers). We were really impressed by the way it worked.