Backup MySql Docker Container

Here is how you can make mysqldump on container that created from mariadb image

docker run -it --link db_1:mysql --rm mariadb sh -c 'exec mysqldump -h"$MYSQL_PORT_3306_TCP_ADDR" -P"$MYSQL_PORT_3306_TCP_PORT" -uroot -p"$MYSQL_ENV_MYSQL_ROOT_PASSWORD" wordpress' > /backup/wordpress-$(date +\%F).sql

This command does the following:

  1. creates new container from mariadb image
  2. configure a link to your db container (db_1)
  3. run the mysqldump command inside the new container
  4. save the output of mysqldump command to a file
  5. remove the new container

Create mysql replication

Create mysql replication is a simple procedure that usually can be done with the following steps:

  1. enable bin-log on your master
    /etc/my.cnf
    [mysqld]
    # Replication
    server-id = 1
    relay-log = mysql-relay-bin
    log-bin=mysql-bin
  2. create replication user
    mysql
    mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
    mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';
    
  3. lock your database and write master position
    mysql> FLUSH TABLES WITH READ LOCK;
    mysql> SHOW MASTER STATUS;
  4. take mysql dump of the database
    mysqldump --all-databases --master-data > fulldb.dump
  5. unlock the database
    mysql> UNLOCK TABLES;
  6. prepare mysql slave server
    /etc/my.cnf
    [mysqld]
    server-id=2
    relay-log = mysql-relay-bin
    log-bin=mysql-bin
  7. restore mysql data
    mysql < fulldb.dump
  8. start replication on the slave server with the change master command
    mysql> CHANGE MASTER TO
        ->     MASTER_HOST='master_host_name',
        ->     MASTER_USER='replication_user_name',
        ->     MASTER_PASSWORD='replication_password',
        ->     MASTER_LOG_FILE='recorded_log_file_name',
        ->     MASTER_LOG_POS=recorded_log_position;
    
    mysql> START SLAVE;

but if you have very big database let say 1TB and you can’t except downtime?

If you prepare right you storage or you are using cloud services then you can lock the database for a few seconds take a snapshot and then copy the data from the snapshot.

if you didn’t prepare right mysql storage then you need to use the right flags in mysqldump command.

These are the flags that I used (relevant for transactional DB like InnoDB):

mysqldump --all-databases --master-data=2 --single-transaction --quick | gzip > outputfile.sql.gz
--all-databases - Used to backup all the databases in mysql server
--master-data=2 - Writes binary log name and position in mysql remark to the dump file
--single-trasaction - This is an important flag that send start trasaction to the mysql server and dump the consistent state of the database at the time when start transaction started. this flag let you use the database while the dump is running. The flag is usefull only for transactional tables like InnoDB.
--quick - Used for large tables to retrieve rows from a table one raw at a time instead of retrieving the entire row set and buffer it in memory before writing it.

To me the dump took about a day and then I restore it with the following command:

gunzip -c outputfile.sql.gz | mysql

The restore took me much longer, it was about 4-5 days. If you have other methods to make the dump or restore faster please let me know.

After the restore we need to run the change master command so we need to grub it from the dump file:

zcat all_db.sql.gz | head -n 200 | grep "CHANGE MASTER"

mysql
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.xxxx', MASTER_LOG_POS=1111133333;
mysql> start slave;

To check the slave status use the following command:

mysql> SHOW SLAVE STATUS\G;

check that Slave_IO and Slave_SQL are running and wait for the Seconds_Behind_Master to decrease to 0 (to me it took ~4 days).

On the new slave server that I created I installed LVM with enough free space for snapshots so next time I can do the following:

  1. lock mysql databases
  2. flush the tables
  3. get master binary log file and position
  4. create LVM snapshots
  5. unlock mysql databases
  6. rsync the data to another server

These steps should take much less time then mysqldump and restore.

During this work I got help from the following links:

  1. mysql docs – http://dev.mysql.com/doc/refman/5.7/en/replication-howto.html
  2. mysql docs – https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_quick
  3. server fault – http://serverfault.com/questions/220322/how-to-setup-mysql-replication-with-minimal-downtime

 

Mysql authenticate using login-path

Mysql version: 5.6.6

Today I wanted to write a script that dump DB from remote mysql server, import to staging and run this script every week.

I didn’t want to write mysql credential in the script so I search for alternative and I found the mysql-config-editor .

To use it I needed to create two configuration: production and staging and I did it like that:

mysql_config_editor set --login-path=prod-db --host=proddb.nachum234.com --user=humus --password
mysql_config_editor set --login-path=localhost --user=humus --password

The command above create encrypted file in current user home directory called .mylogin.cnf.

To check the configuration you can use the following command:

mysql_config_editor print --all

To use this config file just use the –login-path in mysql commands.
Examples:

mysqldump --login-path=prod-db db_name > prod_db_name-$(date +%F).sql
mysql --login-path=localhost db_name < prod_db_name-$(date +%F).sql

003. OpenLDAP Post Install

Introduction

Here I will try to document all my customizations for new OpenLDAP server

Prerequisite

Post Installation

Configure access to cn=config by your root user

  • In phpLDAPadmin login to cn=config
  • Go to cn=config -> olcDatabase={0}config
  • Add in olcAccess value  the root DN of your LDAP root DN
{0}to *  by dn.base="gidNumber=0+uidNumber=0,cn=peercred,cn=external,cn=auth" manage by dn.base="cn=root,dc=humus234,dc=com" manage by * none

Configure access to monitor database so we can monitor OpenLDAP server

  • In phpLDAPadmin login as cn=config or your DN
  • Go to cn=config -> olcDatabase={1}monitor
  • Change in olcAccess value  the root DN to your LDAP root DN
{0}to *  by dn.base="gidNumber=0+uidNumber=0,cn=peercred,cn=external,cn=auth" read  by dn.base="cn=root,dc=humus234,dc=com" read  by * none

Now you can login to your LDAP domain using your root DN and click on monitor to see the monitor DB data of your OpenLDAP server

Configure log level of OpenLDAP server

  • In phpLDAPadmin login to cn=config
  • Go to cn=config
  • Click on “Add new attribute”
  • Choose olcLogLevel and enter stats for basic stats logging

Change database directory

You can change your database directory to a new mounted disk to get better performance

  • In phpLDAPadmin
  • Go to cn=config -> olcDatabase={2}hdb
  • Change olcDbDirectory to your new mounted folder

Change database cache size

You can change the number of entries of the in-memory cache maintain by your DB

  • In phpLDAPadmin
  • Go to cn=config -> olcDatabase={2}hdb
  • Change olcDbCacheSize to your needs

Change database cache size for indexes

You can change the number of entries of the in-memory cache, in index slot maintain by your DB

  • In phpLDAPadmin
  • Go to cn=config -> olcDatabase={2}hdb
  • Change olcDbIDLcacheSize to your needs

Specify indices

You can specify indices to maintain for a given attribute

  • In phpLDAPadmin
  • Go to cn=config -> olcDatabase={2}hdb
  • Add the following values to olcDbIndex
olcDbIndex: default pres,eq
olcDbIndex: uid
olcDbIndex: cn,sn pres,eq,sub
olcDbIndex: objectClass eq

Change database configuration setting

You can change specific database configuration setting to get more performance. Idealy you should set DB cache size to be as large as your working set of database, the log buffer size should be large enough for most transactions without  overflowing, and the log directory should be on a separate physical disk from the main database files.

  • In phpLDAPadmin
  • Go to cn=config -> olcDatabase={2}hdb
  • Add the following values to olcDbConfig:
{0}set_cachesize 0 10485760 0
{1}set_lg_bsize 2097152
{2}set_lg_dir /var/db/disk2/bdb-log

Please visit http://www.openldap.org for more information about OpenLDAP project.

002. phpLDAPadmin Installation

Introduction

phpLDAPadmin is a web based LDAP client. It provides easy management interface for LDAP servers.

You can use phpLDAPadmin to manage different LDAP servers, but in this guide I will assume the use of  phpLDAPadmin to manage OpenLDAP server.

Tested On

OS: CentOS 6.3 x86_64
OpenLDAP version: slapd 2.4.23
phpLDAPadmin version: 1.2.2
Hardware: Virtual Machine (VirtualBox 4.2.4)

Prerequisite

  • Check that you have access to your OpenLDAP server schema with using an anonymous bind
ldapsearch -xLLLh localhost -b '' -s base subschemaSubentry

Procedure

  • Install EPEL repository
rpm -ihv http://mirror.switch.ch/ftp/mirror/epel/6/i386/epel-release-6-7.noarch.rpm
  • Install phpLDAPadmin
yum install phpldapadmin -y
  • Configure phpLDAPadmin to use Distinguish Name (DN) for login attribute (comment out the configuration line of uid login attribute)
vi /etc/phpldapadmin/config.php
...
// $servers->setValue('login','attr','uid');
...
  • Start apache and configure it to start at boot
service httpd start
chkconfig httpd on
  • Browse to phpLDAPadmin application http://phpldapadmin-server-ip/phpldapadmin login with your admin DN (e.g. cn=root,dc=humus234,dc=com) and password and start manage your OpenLDAP server using phpLDAPadmin

Managing OpenLDAP configuration through phpLDAPadmin

If you want to manage your OpenLDAP dynamic configuration (slapd.d directory) using phpLDAPadmin you need to do the following:

  • Generate new password for cn=config
slappasswd -h {MD5}
  • Create the following ldif file that will add root password for cn=config database. Copy the generated password from the last step to olcRootPW attribute.
vi /tmp/add_admin.ldif
dn: cn=config
changetype: modify

dn: olcDatabase={0}config,cn=config
changetype: modify
add: olcRootPW
olcRootPW: {MD5}vJ5A/BrpqbnekVueDrcXiQ==
  • Using root user add the new ldif file to OpenLDAP configuration
ldapadd -Y EXTERNAL -H ldapi:/// -f /tmp/add_admin.ldif
  • Check OpenLDAP configuration for new cn=config RootPW attribute
ldapsearch -LLL -Y EXTERNAL -H ldapi:///  -b cn=config | less
  • Configure LDAP domains in phpLDAPadmin configuration file. If you don’t do this phpLDAPadmin will not recognize cn=config domain
vi /etc/phpldapadmin/config.php
/* Array of base DNs of your LDAP server. Leave this blank to have phpLDAPadmin
   auto-detect it for you. */
// $servers->setValue('server','base',array(''));
$servers->setValue('server','base',array('cn=config','dc=humus234,dc=com')); 

Browse to phpLDAPadmin application http://phpldapadmin-server-ip/phpldapadmin login with cn=config DN and password and start configure your OpenLDAP server using phpLDAPadmin

Please visit http://phpldapadmin.sourceforge.net for more information about phpLDAPadmin project.

001. OpenLDAP Server Installation

Introduction

OpenLdap is an open source implementation the Lightweight Directory Access Protocol. You can use when you need data to be centrally manged, stored and accessible via standards based method. Some common use cases are: users and groups management and authentication, address book, telephony information store and more.

Tested On

OS: CentOS 6.3 x86_64
OpenLDAP version: slapd 2.4.23
Hardware: Virtual Machine (VirtualBox 4.2.4)

Procedure

  • Install OpenLDAP server and client
yum install openldap-servers openldap-clients -y
  • Remove the default configured backend
rm -f /etc/openldap/slapd.d/cn\=config/olcDatabase\=\{2\}bdb.ldif
  • Start slapd service and configure it to start on reboot
service slapd start
chkconfig slapd on
  • Create new backend configuration file (change olcSuffix olcRootDN and olcRootPW to your needs)
vi /tmp/backend.ldif
dn: olcDatabase=hdb,cn=config
objectClass: olcDatabaseConfig
objectClass: olcHdbConfig
olcDatabase: hdb
olcSuffix: dc=humus234,dc=com
olcDbDirectory: /var/lib/ldap
olcRootDN: cn=root,dc=humus234,dc=com
olcRootPW: secret
olcDbCacheSize: 1000
olcDbCheckpoint: 1024 10
olcDbConfig: set_cachesize 0 10485760 0
olcDbConfig: set_lg_bsize 2097152
olcDbIDLcacheSize: 3000
olcDbIndex: objectClass eq
  • Add the new backend configuration file to OpenLDAP
ldapadd -Y EXTERNAL -H ldapi:/// -f /tmp/backend.ldif
  • Create configuration file for your first entries of your created backend (change dn and user password to your backend configuration)
vi /tmp/first_entries.ldif
# Create top-level object in domain
dn: dc=humus234,dc=com
objectClass: top
objectClass: dcObject
objectclass: organization
o: Humus234 Organization
dc: Humus234
description: LDAP Humus234

# Admin user.
dn: cn=root,dc=humus234,dc=com
objectClass: simpleSecurityObject
objectClass: organizationalRole
cn: root
description: LDAP administrator
userPassword: secret
  • Add to new file entries to OpenLDAP
ldapadd -x -D cn=root,dc=humus234,dc=com -W -f /tmp/first_entries.ldif

Useful Commands

  • Search for your entries in OpenLDAP backend
ldapsearch -xLLL -b "dc=humus234,dc=com"
  • Check OpenLDAP configuration
ldapsearch -LLL -Y EXTERNAL -H ldapi:/// -b cn=config dn

OpenLDAP installation completed. You can now install phpLDAPadmin as web client interface to manage your OpenLDAP server.

Here is a link for phpLDAPadmin installation.

Please visit http://www.openldap.org/ for more information about OpenLDAP project.