Install and configure MySQL for Gerrit

Requirements

Centos 6 or other Linux operating system
Root/sudo access to Servers.

Installation

Install the mysql yum repo:

root:~$ wget http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
root:~$ yum install -y mysql-community-release-el6-5.noarch.rpm

Install and start mysql-server:
```
# yum install -y mysql-server
# service mysqld start
```
To set admin or root password on SQL Server use either of the below commands
```
# mysqladmin -u root password 'new_password'
```
Security warning
It's normal to see warnings when you enter plain text passwords:
"Warning: Using a password on the command line interface can be insecure.".

or
```
# mysql-secure-installation
```
- for interactive admin setup. from here this script will set root user and password on the sql database Login to mysql
```
# root:~$ mysql-secure-installation
```
The script will set root user and password for the Mysql database.

Test root login to mysql.

# mysql -h localhost -u root --password=<password>

Use a local LDAP authority
As we run without LDAP account caching there will be a greater burden placed on your LDAP authority as it deals with all account lookups. For this reason we strongly recommend that you ensure that the LDAP authority is hosted locally rather than via a WAN link which would could result in a significant drop in performance.

Configuration of Master/Slave replication of MySQL Database

Setup Master Server

Once MySQL is installed, edit /etc/my.cnf, editting the following lines.
```
server-id=1
log-bin=mysql-bin
```
- The master node will always be server id 1.
- log-bin will log all actions on master and the contents of this file are replicated to the slaves, location can be customised.
- You can also add the optional parameter binlog_do_db = <databasename> to only replicated a single database other wise all actions and new database creation is replicated.
Database Engine

The default engine is now (since MySQL 5.5) InnoDB which replaced the previous MylSAM engine.

Reset the engine to MyISAM

To use the previous default DB engine, MyISAM, add the following line to /etc/my.cnf: default-storage-engine=MYISAM
Recommended configuration for InnoDB Engine

Consider this configuration as a serving suggestion only. You may need additional configuration and you'll need to ensure that system paths are corrected before you try and copy this into your production environment. Take note of the variables in bold, they are changed from the default values.
```
[mysqld]
server-id=1
port=3306
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

key_buffer = 1024M
max_allowed_packet = 16M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
thread_cache_size = 8
query_cache_size = 300M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
tmp_table_size=30M

join_buffer_size = 128M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# use the right value in the next setting
innodb_data_home_dir=/usr/local/mysql/innodb/
innodb_data_file_path = ibdata1:500M:autoextend:max:1000M
innodb_buffer_pool_size=1G
innodb_file_per_table=1
innodb_additional_mem_pool_size=20M
innodb_log_file_size=250M
# use the right value in the next setting
innodb_log_group_home_dir=/usr/local/mysql/innodb/
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=120
innodb_thread_concurrency=4    
```
innodb_lock_wait_timeout
The property innodb_lock_wait_timeout has been increased from the default "50" to "120" milliseconds. This increase prevented lock timeout and deadlock problems during WANdisco's testing. Bare in mind that your own production environment / Gerrit usage may require a different setting.

innodb_buffer_pool_size
Setting 1GB buffer pool size is relatively modest, this is factored on the Gerrit only using direct selects to single tables and never needing to perform large multi-table joins that tend to inflate memory requirements.

innodb_file_per_table
When enabled (=1), InnoDB stores the data and indexes for each newly created table in a separate .ibd file, rather than in the system tablespace. More about innodb_file_per_table

In each case you should evaluate the requirements of your own production environment and modify the properties accordingly.
Restart MySQL
```
service mysqld restart
```

Setup of Slave Servers

Repeat the Installation of MySQL instructions on all slave servers.
Edit /etc/my.cnf and add edit following lines.
```
server-id=<previous_node server-id + 1>
```
Unique nodes
Each node must have a unique server-id number.
Following this restart MySQL on each server:
```
root:~$ service mysqld restart
```

Create user for Replication

root:~$ mysql -h localhost -u root --password=<password>

Enter the following commands from mysql prompt:

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

Getting Master's Binary log location

To make sure replication begins at the right point will need to get the current position of the master log.

Login to the Master Server with root credentials (in the event you have logged out since the previous instruction).
```
mysql> mysql -h localhost -u root --password=<password>
```
Enter following commands to stop write actions and return current log location:
```
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
```

This will return the current log file and the position of the cursor on it.

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      411 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Note these details
Take note of these details as they are required in the final configuration of the slave nodes.

edit MySQL
```
mysql> exit;    
```

Backing up Current Databases

If are converting a single Server into a replicated master server, backup of the database structure to the Slave nodes is required prior to replication.

To do this, run the following command on the master node and move the resulting file onto each slave.
```
root:~$ mysqldump -uroot -proot reviewdb > reviewdb.dmp
```
Replacing reviewdb with the database you wish to backup.

Starting Slaves from fresh installation (No pre-existing Data)

Start MySQL on each Slave server and log into mysql as root:
```
root:~$ mysql -h localhost -u root --password=<password>
```

Enter the following with details to match that of your Master Server.

mysql> CHANGE MASTER TO
       MASTER_HOST='<master_host_name>',
       MASTER_USER='repl',
       MASTER_PASSWORD='slavepass',
       MASTER_LOG_FILE='mysql-bin.000001',
       MASTER_LOG_POS=411;

Master_Log_File is blank as no previous logging and Master_log_pos to 4 for same reason.
Once this as been completed on all Slaves start the slave replication process with the below command:
```
root:~$ mysql -u root --password=<password> -e "START SLAVE;"
```
This will start replication any new Write actions to the master server will now be replicated to the slaves.

Starting Slaves with pre-existing data

Start by releasing the read lock on the Master Server, by logging in to master server and running below command:
```
mysql> UNLOCK TABLES;
```
Start the Slaves with the " --skip-slave-start" flag to avoid replication
```
root:~$ sudo mysqld start --skip-slave-start
```

Import the Dump file created from master:

root:~$ mysql -uroot -proot reviewdb < reviewdb.dmp

Replacing reviewdb with the database you backed up from master

Run the Change master command by logging into each slave and running the below command:

       mysql> CHANGE MASTER TO
       MASTER_HOST='<master_host_name>',
       MASTER_USER='<replication_user_name>',
       MASTER_PASSWORD='<replication_password>',
       MASTER_LOG_FILE='<logFile>',
       MASTER_LOG_POS=<log_postion>;

With the Logfile path and log position collected from master during the previous steps

Following completion on each slave, start the slave threads to begin replication.
```
root:~$ mysql -u root --password=<password> -e "START SLAVE;"
```
This will start replication of all new write commands to the master node.

Configuration of the MySQL Proxy

MySQL Proxy is a proxy that can sit infront of a MySQL slave to redirect write commands back to the master server and pull reads locally from the Slave server.

Installation

Following the Installation and configuration of Master/Slave enviroment:

Download the mysql-proxy tar file from here :MySQL:MySQL-proxy
Untar the Downloaded file onto each slave

Run the mysql-proxy script from <untar target>/bin/mysql-proxy

root:~$ ./mysql-proxy --proxy-backend-addresses=<Master_nodesIP>:3306 --proxy-read-only-backend-addresses=<slavesIP>:3306 --proxy-lua-script=<untar_target>/share/doc/mysql-proxy/rw-splitting.lua --daemon --keepalive

This will start the proxy on port 4040 so applications that connect to the MySQL database should be directed to this port instead.

--proxy-backend-addresses contains all Read/Write servers e.g. the master server
--proxy-read-only-backend-addresses contains Read only servers e.g. slaves
--proxy-address=host:port can be used to specify a different proxy port and hostname if required.
--proxy-lua-script=<untar_target>/share/doc/mysql-proxy/rw-splitting.lua is used to split reads and writes between read only slaves and master.
--daemon starts proxy in daemon mode.
--keepalive restarts proxy on error.

Full list of command line flags here :MySQL-proxy cmd line

Repeat this step on all required slaves and any requests sent through proxy will be directed appropriately with writes going to the master node and reads coming from the local slave server.

Install and configure MySQL for Gerrit

Requirements

Installation

Configuration of Master/Slave replication of MySQL Database

Setup Master Server

Reset the engine to MyISAM

Recommended configuration for InnoDB Engine

Setup of Slave Servers

Create user for Replication

Getting Master's Binary log location

Backing up Current Databases

Starting Slaves from fresh installation (No pre-existing Data)

Starting Slaves with pre-existing data

Replacing reviewdb with the database you backed up from master

Configuration of the MySQL Proxy

Installation