Category Archives: mysql

How to set MySQL permissions read-only with exceptions

The infrastructure about MySQL Master Slave replication involves:

  1. MySQL custom configuration
  2. MySQL custom recall
  3. Optimization and performance about point 1 and 2

Often there are open source that customizes MySQL recall, and it is not simple to adapt a MySQL Master Slave infrastructure. So that any simple idea is considered.

More open source uses user session, and table about session could be unique problem about a system of Master Slave replication with more slave.

Each slave could permit a read-only permission for each table excluding table session with these MySQL commands:

grant select on database_name.* to 'mysql_user'@'localhost';
grant all on database_name.table_session to 'mysql_user'@'localhost';

Reference: mysql.com

How to create Master Master replication between two MySQL instances

MySQL Master Master replication is a double MySQL Master Slave replication between two MySQL instances: one MySQL instance (Mi1) is Master and the other MySQL instance (Mi2) is Slave and vice versa.

Minimal configuration file (/etc/my.cnf) of each mysql instance is similar:

[mysqld]
log-bin=/var/log/mysql/binlog.log
expire_logs_days=10
max_binlog_size=100M
datadir=/var/lib/mysql/data
socket=/var/lib/mysql/mysql.sock
innodb_flush_log_at_trx_commit=1
sync_binlog=1
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin

There is few difference.
Mi1
server-id = 1
Mi2
server-id = 2

If the servers are not alternative, and both writes into database, then it is important to be alert to the schema of the tables and it could be important add different auto increment configuration.
Mi1
auto_increment_increment = 2
auto_increment_offset = 1

Mi2
auto_increment_increment = 2
auto_increment_offset = 2

The initializing and starting replication is even to MySQL Master Slave replication between two MySQL instances.

It is important to assess the risks of a desynchronization between two MySQL instances. The errors can arise in the retrieval of information from the slave. If both instance writes into database, the reasons can be many:

  1. MySQL instances create a record considered as identical to the schema of a table (error 1062, duplicate entry)
  2. MySQL instances can not communicate due to lack of network for a time sufficient to change log file
  3. SQL syntax could be contain an error (error 1064, sql syntax)
  4. and the list is endless

It may need to add the slave skip errors configuration into /etc/my.cnf:
slave_skip_errors = 1062,1064

It may need to delete error on the fly with MySQL commands:
stop slave; set global SQL_SLAVE_SKIP_COUNTER=1; start slave;

Reference: mysql.com

How to create replication between two MySQL instances

It is important create a mysql user into each instance that it could exec only replication actions, in mysql shell:
create user replica@localhost;
grant replication slave on *.* to replica@localhost identified by 'replicapassword';

Minimal configuration file (/etc/my.cnf) of each mysql instance is:
master
[mysqld]
server-id=1
log-bin=/var/log/mysql/binlog.log
expire_logs_days=10
max_binlog_size=100M
datadir=/var/lib/mysql/data
socket=/var/lib/mysql/mysql.sock
innodb_flush_log_at_trx_commit=1
sync_binlog=1

slave
[mysqld]
server-id=2
master-host=ip.server.master
master-port=3306
master-user=replica
master-password=replicapassword
log-bin=/var/log/mysql/binlog.log
expire_logs_days=10
max_binlog_size=100M
datadir=/var/lib/mysql/data
socket=/var/lib/mysql/mysql.sock
innodb_flush_log_at_trx_commit=1
sync_binlog=1
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin

Last step is initializing and starting replication. In mysql shell:
master
reset master;
slave
change master to master_host='ip.server.master',
master_user='replica',
master_password='replicapassword',
master_port=3306;

If you want resetting replication:
master
reset master;
slave
stop slave;
reset slave;
start slave;

If you want show which it is binary file and position:
show master status;
show slave status;

Reference: mysql.com

How to install multiple MySQL instances on the same machine

There are various methods to run multiple instances of mysql (on different ports) on the same machine. We can either compile the mysql binary with different defaults and paths, use mysqld_multi or the MySQL Sandbox project. Still the simplest solution I’ve used in the past for such situations is to use the same binary and use a separate configuration file (with separate port, pid, socket and data directory). This post will explain this method.

Create separate data and log directories
We need to create new directories for our datadir and log folder (if used). Also we need to assign proper permissions on those folders:
mkdir /var/lib/mysql2
chown -R mysql:mysql /var/lib/mysql2/
mkdir /var/log/mysql2
chown -R mysql:mysql /var/log/mysql2

Create a new mysql configuration file
Next we need a separate configuration file. We can start by copying the existing one and changing the needed values. This example was taken on a debian machine that holds the mysql configurations under /etc/mysql/my.cnf. We just copy this folder and modify it from there:
cp -R /etc/mysql/ /etc/mysql2
if you use a redhat variant package then your configuration file is under /etc/my.cnf by default and you can just copy it directly:
cp /etc/my.cnf /etc/my2.cnf
(or change the path appropriately for your configuration file is in a different place).
Next, we need to edit our new configuration file and at least update the mysql port (default to 3306), the pid and socket to be different than the default ones, and also point the data and log folders to the ones created before.
cd /etc/mysql2/
sed -i 's/3306/3307/g' my.cnf
sed -i 's/mysqld.sock/mysqld2.sock/g' my.cnf
sed -i 's/mysqld.pid/mysqld2.pid/g' my.cnf
sed -i 's/var\/lib\/mysql/var\/lib\/mysql2/g' my.cnf
sed -i 's/var\/log\/mysql/var\/log\/mysql2/g' my.cnf

Initializing and starting
Finally we need to initialize the default dbs:
mysql_install_db --user=mysql --datadir=/var/lib/mysql2/
Alternatively we can copy the existing /var/lib/mysql if this is needed (shut down mysql prior to do this).
Finally we can start our new mysql instance with:
mysqld_safe --defaults-file=/etc/mysql2/my.cnf &
We can connect to our new instance using:
mysql -S /var/run/mysqld/mysqld2.sock
or
mysql -h 127.0.0.1 -P 3307
and if we no longer need it, stop it with:
mysqladmin -S /var/run/mysqld/mysqld2.sock shutdown

Other solution
Create a unique mysql configuration file
You could create unique mysql configuration file /etc/my.cnf. You could label each instance with a number and define same parameters into unique file.
If you have got two instances, 1 and 2, your minimal mysql congifuration file could be this:

[mysqld1]
set-variable=local-infile=0
datadir=/var/lib/mysql1
socket=/var/lib/mysql1/mysql.sock
user=mysql
old_passwords=1
bind-address=127.0.0.1
[mysqld_safe1]
log-error=/var/log/mysql1/mysqld.log
pid-file=/var/run/mysqld/mysqld1.pid

[mysqld2]
set-variable=local-infile=0
datadir=/var/lib/mysql2
socket=/var/lib/mysql2/mysql.sock
user=mysql
old_passwords=1
bind-address=127.0.0.1
[mysqld_safe2]
log-error=/var/log/mysql2/mysqld.log
pid-file=/var/run/mysqld/mysqld2.pid

Each directory had owner mysql user:
chown -R mysql:mysql /path/directory

Initializing and starting
As above. Or you could use mysqld_multi command line to start mysql instances together:
mysqld_multi --verbose --log=/var/log/mysqld.log start 1,2

About stop instances, you could use mysqladmin command line as above. If you
need a particular mysql user about shutdown particular mysql instance, you could use:

mysqladmin --defaults-file=/etc/myuser.cnf shutdown

where /etc/myuser.cnf is:

[client]
user=myuser
password=myuserpassword
socket=/var/lib/mysql1/mysql.sock

and you must add in mysql shell:
GRANT SHUTDOWN ON *.* TO 'myuser'@'localhost' IDENTIFIED BY 'myuserpassword'

Check port of MySQL instances
If you have worked well, then you have got two MySQL instances that they listen on different ports:
netstat -ntl
My output is:
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:3307 0.0.0.0:* LISTEN

Reference: ducea.com

max_connections system variable

The number of connections permitted is controlled by the max_connections system variable. How to modify max_connections value:
/etc/init.d/mysqld stop
mysqld_safe -O max_connections=1000 &
mysqladmin variables -u admin -ppassword | grep max_connections

or into /etc/my.cnf under the [mysqld] section add:
max_connections = 500
or log in mysql as root and execute:
SET GLOBAL max_connections = 500;
and restart MySQL:
/etc/init.d/mysqld start

Reference:dev.mysql.com