WANdisco
 Navigation:  v | Release Notes | Integration | Install | Integration | Administration | Git MultiSite | Glossary |

Install and configure MySQL for Gerrit

1. Requirements

2. Installation

  1. 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
  2. Install and start mysql-server: 
    # yum install -y mysql-server
# service mysqld start
  3. 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, e.g:
    Warning: Using a password on the command line interface can be insecure.

    or for interactive admin setup: 
    # mysql-secure-installation
    This script sets root user and password on the sql database.
  4. Log in to mysql: 
    # root:~$ mysql-secure-installation
    This script sets root user and password for the Mysql database.
  5. Test root login to mysql: 
    # mysql -h localhost -u root --password=<password>

    6. 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.

3. Configure master/slave replication of MySQL database

3.1 Set up master server

  1. When MySQL is installed, edit the following lines in /etc/my.cnf: 
    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 replicate a single database, otherwise all actions and new database creation is replicated.

    Database engine

    The default engine is now, since MySQL 5.5, InnoDB. This 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 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. Note that the variables in bold 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. Remember that your own production environment and Gerrit usage may require a different setting.

    innodb_buffer_pool_size
    Setting a 1GB buffer pool size is relatively modest. This is factored on 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. See more information about innodb_file_per_table.


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

3.2 Set up slave servers

  1. Repeat the Installation of MySQL instructions on all slave servers.
  2. Edit the following lines in /etc/my.cnf:
    server-id=<previous_node server-id + 1>

    Unique nodes
    Each node must have a unique server-id number.

  3. Restart MySQL on each server: 
    root:~$ service mysqld restart

3.3 Create user for replication

  1. Log in to the Master Server with root credentials: 
    root:~$ mysql -h localhost -u root --password=<password>
  2. Enter the following commands from mysql prompt: 
        mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
    mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

3.4 Get Master's binary log location

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

  1. If you have logged out, log in to the Master Server with root credentials: 
    mysql> mysql -h localhost -u root --password=<password>
  2. Enter following commands to stop write actions and return the current log location: 
    mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
    This returns 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.

  3. edit MySQL 
    mysql> exit;

3.5 Back up current databases

If are converting a single Server into a replicated master server, you must back up the database structure to the Slave nodes before replication:

  1. Run the following command on the master node and move the resulting file onto each slave: 
    root:~$ mysqldump -uroot -proot reviewdb > reviewdb.dmp
    Replace reviewdb with the database you want to backup.

3.6 Start Slaves from fresh installation with nNo pre-existing data

  1. Start MySQL on each Slave server and log into mysql as root: 
    root:~$ mysql -h localhost -u root --password=<password>
  2. 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;
  3. Master_Log_File is blank because no previous logging and Master_log_pos to 4 for same reason.
  4. When this is complete on all Slaves, start the slave replication process with the command: 
    root:~$ mysql -u root --password=<password> -e "START SLAVE;"
    This starts replication. Any new write actions to the master server are now replicated to the slaves.

3.7 Start Slaves with pre-existing data

  1. Release the read lock on the Master Server by logging in to the master server and run the command: 
    mysql> UNLOCK TABLES;
  2. Start the Slaves with the --skip-slave-start flag to avoid replication: 
    root:~$ sudo mysqld start --skip-slave-start
  3. Import the Dump file created from master: 
    root:~$ mysql -uroot -proot reviewdb < reviewdb.dmp

3.8 Replace reviewdb with database backed up from master

  1. Run the Change master command. Log into each slave and run the following command with the logfile path and log position collected from master in the previous steps: 
             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>;
  2. Following completion on each slave, start the slave threads to begin replication: 
    root:~$ mysql -u root --password=<password> -e "START SLAVE;"
    This starts replication of all new write commands to the master node.

3.9 Configure the MySQL Proxy

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

After you install and configure the Master/Slave enviroment:

  1. Download the mysql-proxy tar file from MySQL:MySQL-proxy.
  2. Untar the downloaded file onto each slave.
  3. 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 starts 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.
    See a full list of MySQL proxy command-line flags.
  4. Repeat this step on all required slaves. Any requests sent through proxy will be directed appropriately, with writes going to the master node and reads coming from the local slave server.