Tag: centos

Backup, Restore and work on PostgreSQL cmd line

Last time, we have a look how to install and set up PostgreSQL on a CentOS machine as well as to create a database. Let’s now see some more details about backups and database restore as well as some commands to master the basic of psql on command line.

Screenshot from 2016-03-31 05-31-15

1.We have created a database called test. Here is an example  how you create a table on Postgres

test=# create table hackers( id int primary key not null, country char(20), number int not null, project varchar(50) );

Screenshot from 2016-03-31 05-41-13

2. You can view your table with the command \d or \d <table name>

Screenshot from 2016-03-31 05-42-38

3. To perform a dump of a database log in as the user which have access to the database and launch the following command.

pg_dump test > db_backup.sql

To restore the same backup on a postgres machine, you need to have the database created first

psql test < db_backup.sql

4. A little work around of psql around the database for example accessing directly the database test with the command psql test and to list all the database can be done with \l

Screenshot from 2016-03-31 06-04-24

5. Let’s say you want to switch between database test and toto. This can be done with \c test and \c toto

Screenshot from 2016-03-31 06-07-05

6. To show all tables inside the database can be done with the command \dt

Screenshot from 2016-03-31 06-08-30

7. The schema of the table can also be seen with the command \d+ <table name> Here, it will be \d+ hackers

Screenshot from 2016-03-31 06-11-19

8. All users and permissions can be viewed with \du

Screenshot from 2016-03-31 06-15-18

9. Databases are create and drop inside the psql command line ending with a semi column

Screenshot from 2016-03-31 06-19-19

10. You can also alter user role with superuser and non superuser

Screenshot from 2016-03-31 06-21-51

11. You can also output the result of a select statement from the psql prompt directly using the following (For example the to output the result of a select statement to ~/SelectStatement.out)  :

postgres=# \o ~/SelectStatement.out - Start logging the output
postgres=# Select * from users;     - The command
postgres=# \o                       - Stop the logging

 

Install, Setup and Create DB on PostgreSQL

PostgreSQL is yet another open source object relational database system. Its compatible with almost all operating system including BSD, Windows and Linux. I will be using a Centos7 machine to install a PostgreSQL and set up some basics of PostgreSQL. In a next article, i will give some idea of something more robust you can do with PostgreSQL.

Photo Credits: postgresql.org
Photo Credits: postgresql.org

1.You can install PostgreSQL from the repository with the following command

yum install postgresql-server postgresql-contrib

2. Now the first action you need to perform after PostgreSQL server installation is to initialize the database by creating a new database cluster

postgresql-setup initdb

3. You can now start the postgresql service. Postgres will be listening on port 5432

systemctl start postgresql

4. Now that we have PostgreSQL installed on our machine, we can create the first super user with following command. Let’s called the user test. Its a practice to use the username having sudo privilege on the machine itself.

sudo -u postgres createuser --superuser  test

5. To connect on the Postgres command line use the following. postgres is the default user and psql is what u what to run

sudo -u postgres psql

6. You should have something similar to this.

Screenshot from 2016-03-30 21-21-11

7. Now you can set the password for the superuser that you have just created. In my case it is user ‘test’ at step 4

\password test

8. Once you have been prompted to enter the password twice means that you have already set up PostgreSQL. You can exit with the command

\q

9. To connect to the default postgres database you simply need to use the command. To quit follow step6

psql postgres

10. Lets create a database with the superuser. You might need to add your user to the group postgres with the command usermod -g test postgres as postgres will need permission to access your home directory to drop the .psql_history file

sudo -u postgres createdb test

11. To get on the command line you just need to type psql which should show you something similar to this

Screenshot from 2016-03-30 21-25-49 

MariaDB-10.1 Galera Cluster on CentOS 7

Some times back, i posted two articles on MariaDB Master-Master replication and MariaDB Master-Slave replication. Well, after several requests from friends, i was asked to blog on MariaDB Galera Cluster. MariaDB Galera Cluster is a synchronous multi-master cluster for MariaDB. It is available on Linux only, and only supports the XtraDB/InnoDB storage engines (although there is experimental support for MyISAM. – Mariadb.com. You can easily download Galera Cluster using the Repository Configuration tool. There is also a slight difference between oldest version of MariaDB compared to that of the 10.1 series. Recently, a question was asked on ServerFault to which i clarified same. Check it out for more information. Lets now have some funs with MariaDB Galera Clustering.

Photo credits:Mariadb.org
Photo credits:Mariadb.org

I have created 3 CentOS7 virtual machines on Virtual Box. Lets called each Server as Galera1 [192.168.1.9], Galera2 [192.168.1.10] and Galera3[192.168.1.11]. You need to edit the /etc/hosts file to allow each machine to ping each others via hostname. For testing purposes port 3306 which is by default listening on MySQL should be open. I have also disable SELinux and Firewalld as same are for testing purposes. Lets now install MariaDB Galera Cluster.

1.Edit the repository by using configuration as mentioned on the official MariaDB website.

# MariaDB 10.1 CentOS repository list - created 2015-11-08 17:34 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

2. Lets install some Pre-requisites and other interesting tools.

yum install rsync nmap lsof perl-DBI nc

3. Install MariaDB-server and start the Mariadb service

yum install MariaDB-server MariaDB-client MariaDB-compat galera socat jemalloc
service mysql start

4. Launch the mysql_secure_installation command to set up the username and password. At this point you can start MariaDB normally by using the mysql command and create a database.

5. After the normal procedure to set up the first database on galera1, you can create a database and dump it, then import the database on galera2 and galera3. You have now 3 independant databases on 3 different servers. I would adivce you to dump the whole database with the command

mysqldump --all-databases > db.sql

6. We will now start setting up the Galera clustering. On galera1, edit the /etc/my.cnf.d/server.cnf file on galera1 and configure it as follows. The server galera1 is the being setup as the first primary cluster. So every other cluster is going to be set up in the network that is galera2 and galera3 is going to replicate itself from galera1. Add the following parameter under the [galera] option.

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address='gcomm://'
wsrep_cluster_name='galera'
wsrep_node_address='192.168.1.9'
wsrep_node_name='galera1'
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

7. Once this is added, you can now start the first Galera cluster with the command. Make sure that the mysql service is stopped first. as at step5 we have started mysql to create a test database

service mysql start

8. You would noticed that the ports 3306 and 4567 should be listening

Screenshot from 2016-03-27 11-23-59

9. Now you would like to make galera2 joined the primary cluster i.e galera1. Simple set up the galera2 /etc/my.cnf.d/server.cnf file and under [galera] enter the following parameter. The only difference is the wsrep_node_name, wsrep_node_address and the gcomm value where you need to add the first cluster.

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address='gcomm://192.168.1.9'
wsrep_cluster_name='galera'
wsrep_node_address='192.168.1.10'
wsrep_node_name='galera2'
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

10. Once mysql is started with the command service mysql start, you would noticed that galera2 is now on replicating from cluster1. You should noticed something similar to this on your log.

Screenshot from 2016-03-27 12-33-56

11. Repeat the same procedure for galera3 by tweaking the gcomm value and modifying the wsrep_node_address etc..

If you interested in having some more ideas about the MariaDB improved security features, do check out the following link where I attended a conference on MariaDB organized by the MSCC.

Tips:

  • If  you are using the 10.0, you would need MariaDB-Galera-server package compared to 10.1 where you just need MariaDB-server.
  • There are no longer separate MariaDB Galera Cluster releases for MariaDB 10.1 and above. Simply download MariaDB (10.1 or above) and configure your cluster as normal. MariaDB-10.1 is just ‘Galera ready’.
  • If you are migrating from MariaDB-server to Galera make sure you have clean up all the residues belonging to the old versions. See http://serverfault.com/questions/646973/installing-mariadb-galera-via-yum-fails-with-no-package-mariadb-galera-server.
  • The galera package will provide you the /usr/lib64/galera/libgalera_smm.so  and several binaries for the cluster configuration. There are several cluster configuration under ws_rep see the documentation.
  • The socat package establishes two birectional byte streams and transfers data between them.
  • I would also recommend to disable SELinux on the machines.
  • When you start the mysql service, you can tail the log at /var/log/messages otherwise you can set up mysql log.
  • Use this command show status like ‘wsrep_%’; on your mysql prompt to understand the replication.
  • To know how much node in the cluster use the following command show status like ‘wsrep_cluster_size’;

Configure your LVM via Ansible

Some days back, I gave some explanations about LVM such as creations of LVM partitions and a detailed analogy of the LVM structure as well as tips for using PVMOVE. We can also automate such task using the power of Ansible. Cool isn’t it?

ansible

So, I have my two hosts Ansible1 and Ansible2. Ansible1 is the controller and has Ansible installed and Ansible2 is the hosts that the disk will be added to the LVM.

1. Here is the status of the disk of Ansible2 where a disk /dev/sdc has been added

Screenshot from 2016-03-08 11-05-29

2. I have now added a disk of 1GB from the VirtualBox settings. You can refer to the past article on LVM how to add the disk. As we can see on the screenshot below it shows the disk sdc with the size 1GB added on the machine Ansible2 which I have formatted as LVM

Screenshot from 2016-03-08 11-22-17

4. Lets now get into the controller machine – Ansible1 and prepare our Playbook. You can view it on my Git account here. The aim is to get a 500Mb from the /dev/sdc1 to create a new VG called vgdata in the LV called lvdisk.

5. Here is the output

Screenshot from 2016-03-08 11-36-00

Articles on LVM

Articles on Ansible

 

Installing, Updating and Playing around with a Docker container

Docker will probably be on heavy development those days. This article will be dedicated to most of the basics to install and update a docker instance and some tips to play around. In the future, I will get into details about Docker Engine, Images, Containers, Volumes and Networking in the context of Docker. I have reserved this blog for the installation of Docker, get updates from the official channel and perform some basic daemon configs. So what is Docker? “Docker provides an integrated technology suite that enables developers and IT operations teams to build, ship, and run distributed applications anywhere.” – Docker

A nice experience is to use a Virtual Box machine running on a Centos machine. My physical machine is running an Ubuntu.

Centos 6 Machine:

1. To install docker do a

yum install docker-io

2. Start the docker service

service docker start

3. check the docker version and new version available as well as the info

docker -v
docker version
docker info

Screenshot from 2016-02-26 23-49-21

4. You can also check for the number of containers, images, storage and execution driver details the command.

docker info

Let’s now see how to update Docker. Prior before performing an upgrade, it’s important to perform a backup of images. To get a new docker version, you will need to add a docker repo to get the new version and launch an update. You can check back the version with the command docker -v Just update your repository and launch an update. Check out the docker docs at this link.

Docker needs root to perform major actions like the creation of namespaces and cgroups. Docker also uses the /var/run/docker.sock which is own by root and is found in the group docker. So normal users can be added to the group docker by keeping security control to the docker group.

Screenshot from 2016-02-27 01-31-14

5. Lets try running an instance with the following command using the user called “nitin”: The command simply means lets run docker run to start a new container -it to make it interactive and assigned it a tty and same will be used by a ubuntu image and a bash process will be run inside the container.

docker run -it ubuntu /bin/bash

6. If the user “nitin” is not found in the group “docker” same would not run. To add the user simply use the following command. As you can see here “nitin” is the user which is being added to the group “docker”

gpasswd -a nitin docker

Now, when the command is being launched you would notice the download in progress.

Screenshot from 2016-02-27 01-45-13

Afterward, you will find yourself landed inside the container itself. Cool isn’t it? I am now inside an Ubuntu container from the Centos Virtual Machine. The number 7fa21bcf66b5 is the short form the container unique ID.

Screenshot from 2016-02-27 01-58-17

Type exit to get back to the Virtual machine. More articles coming later on Docker. 

7. To go on the docker hub and see all container images  related to CentOS:

docker search centos

TIPS:

  • On Centos7 machine, a simple curl -fsSL https://get.docker.com/ | sh would do the installation for the latest version
  • Always create a user for the docker application, the add docker to the group with usermod -aG docker docker-user
  • On CentOS, when firing the docker version | grep Storage, you would noticed that the Default storage driver is ‘device mapper’ compared to Ubuntu which is by default AUFS
  • Docker needs root to work. You can see that a ls -l /var/run/docker.sock is assigned by default with user root and group docker. So normal users can be added to the group dockers to allow then to run and break docker without to be root.