Mysql replication, error 13 (permission denied) with slave_load_tmpdir

MySQLreplication

I've setup a virtual machine on Azure with Ubuntu 16.10 and Mysql 5.7 for a database replication Master/Slave from another server.

I followed this article: https://www.opsdash.com/blog/mysql-replication-howto.html

All seems to works correctly, replication works but I've serious problems with the use of DATA LOAD INFILE. If I use this command, the replication doesn't works.

I tried with relative varible "slave_load_tmpdir", creating a new directory '/var/slavetmp' and updating my.cnf with these values, but this is the error in mysql with "SHOW SLAVE STATUS":

Unable to use slave's temporary directory /var/slavetmp – Can't read dir of '/var/slavetmp/' (Errcode: 13 – Permission denied)

Have any ideas?

Thanks in advance

Best Answer

Assuming that the MySQL user is called "mysql" (check for "user" in my.cnf):

chown mysql.mysql -R /var/slavetmp
Edit /etc/apparmor.d/usr.sbin.mysqld. Copy the lines for /var/lib/mysql and edit the copied lines to refer to /var/slavetmp
Restart AppArmor: /etc/init.d/apparmor restart
Restart the mysql service

Related Solutions

MySQL Master/Slave Replication – Structure Not Replicating

Using replicate-wild-do-table=% is not the correct way to get complete replication. That would seem sensible, but here's why it isn't:

The replicate-* options are restrictive by their presence. The *-do-* options seem to be telling the server what to "do," but in fact they are telling the server what to only do.
The complete absence of any replicate-* configuration variables means "replicate everything."

In the simplest case, when there are no --replicate-* options, the slave executes all statements that it receives from the master. Otherwise, the result depends on the particular options given. -- http://dev.mysql.com/doc/refman/5.5/en/replication-rules.html

That, I think, is the point you are needing. It holds true for all MySQL 5.x.

Once you enable binary logging, set the server-id values on each machine, synchronize the data sets, CHANGE MASTER TO ..., and then START SLAVE, then you should have a working configuration where all DML and DDL will be replicated and your servers will be identical replicas of each other.

If you started out with identical data sets, everything should behave exactly as you would expect it to.

What MySQL replication does best and simplest is replicate entire data sets among servers without any restrictions. Trying to restrict replication to a subset of the data is a process that should carry a warning label that ends with the phrase "...unless you really know what you're doing."

When you use the --replicate-* options, the document cited above also offers this tip:

it is recommended that you avoid mixing “do” and “ignore” options, or wildcard and nonwildcard options

You are using a mix of these, which adds complexity and may explain why your DDL didn't replicate as you expected it to.

MySQL replication – ibdata1 huge

If you want to shrink ibdata1, so that it should only contain the metadata, You may try these steps

To shrink ibdata1 once and for all you must do the following:

MySQLDump all databases into a SQL text file (as bkp_all_db.sql)
Drop all databases (except mysql schema)
Stop MySQL /etc/init.d/mysql stop

Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Now remove ibdata1, so that there should only be the mysql schema in /var/lib/mysql

rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile

Start MySQL instance, This will recreate ibdata1, ib_logfile0 and ib_logfile1 at 1G each

service mysqld start
Reload bkp_all_db.sql into mysql (import). ibdata1 will grow but only contain table metadata

http://mysqlrockstar.blogspot.in/2014/07/mysql-ibdata1-file-shrink.html

Best Answer

Related Solutions

MySQL Master/Slave Replication – Structure Not Replicating

MySQL replication – ibdata1 huge

Related Question