msgbartop
MAC OS X, Linux, Windows and other IT Tips and Tricks
msgbarbottom

20 Dec 16 Switch database type from H2 to MySQL in Atlassian Jira

Introduction:

After having tested Jira and decided to keep it for production it is very recommended to change the type of database used by Jira. The default database at delivery time is H2(local file dB) and in this HOW-TO I describe what I had to do to execute that switch under Debian Jessie.

Steps:

References:
https://confluence.atlassian.com/jira062/switching-databases-588581557.html
https://confluence.atlassian.com/adminjiraserver072/connecting-jira-applications-to-mysql-828787562.html
https://confluence.atlassian.com/jira060/connecting-jira-to-mysql-370705252.html

Backup database:
(SprocketWheelIcon)==>>System ==>>(Left menu)Backup System ==>>Filename: HP_JIRA_Backup_1.zip
Results:
eg. /var/atlassian/application-data/jira/export/HP_JIRA_Backup_1.zip

Create the new Database in MySQL:
Follow theses instructions:
1) Connect to mysql as root:
mysql -p -u root
PW: ******

2) Create the DB, user and user access rights:
CREATE DATABASE jiradb CHARACTER SET utf8 COLLATE utf8_bin;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on jiradb.* TO 'jiradbuser'@'localhost' IDENTIFIED BY '<DBpassword>';
flush privileges;

3) Tip: To confirm if the permissions were granted successfully, log into the DB server with the JIRA DB user and run the command below:
SHOW GRANTS FOR 'jiradbuser'@'localhost';
4) Quit Mysql:
quit;
Stop Mysql server and Jira:
service mysql stop
service jira stop

Delete the innoDB log files
IMPORTANT!! Mke sure you have no other application that uses innoDB format for its databases otherwise that deletion might do data corruption.
rm /var/lib/mysql/ib_logfile?
Edit /etc/mysql/my.cnf and add the following lines:
FOR Mysql 5.5 and below
[mysqld]
...
default_storage_engine=InnoDB
max_allowed_packet=256M
innodb_log_file_size=256M

FOR Mysql 5.6
[mysqld]
...
default_storage_engine=InnoDB
max_allowed_packet=256M
innodb_log_file_size=2G

Remove this if it exists
sql_mode = NO_AUTO_VALUE_ON_ZERO
Start MySQL server:
service mysql start
Install the MySQL JDBC driver to Jira drivers directory
cd /tmp
wget http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.40.tar.gz
tar fvxz mysql-connector-java-5.1.40.tar.gz
cp mysql-connector-java-5.1.40/mysql-connector-java-5.1.40-bin.jar /opt/atlassian/jira/lib/
# Delete the not needed uncompressed directory
rm -rf mysql-connector-java-5.1.40

Connecting Jira to MySQL database:
Rename the dbconfig.xml file as follows:
Note: this change of filename will force Jira to start the Setup Wizzard if it doesn’t find this file.
mv /var/atlassian/application-data/jira/dbconfig.xml /var/atlassian/application-data/jira/dbconfig.xml.H2
Restart Jira:
Note: It is normally a long process that might take up to a minute before Jira can really be ready to be used.
service jira stop && service jira start && tail -f /var/atlassian/application-data/jira/log/atlassian-jira.log
Watch for errors(like ‘exception…’
When the logs are showing something like below then Jira is ready to be used to continue the migration of the database.
---------------------------------------------------------------------------------
Heap memory : Used: 196 MiB. Committed: 482 MiB. Max: 733 MiB
Non-heap memory : Used: 57 MiB. Committed: 59 MiB. Max: 1264 MiB
---------------------------------------------------------------------------------
TOTAL : Used: 253 MiB. Committed: 541 MiB. Max: 1997 MiB
---------------------------------------------------------------------------------

Connecting Jira to the MySQL database:
– Using the browser go to this Jira site and you will be presented with the Jira setup wizzard.
– Select the Manual Setup
– At database Setup page select: My Own Database and fill in the blanks
– Click on Test Connection to verify the validity of the information
– If all ok, click on Continue button.
– Enter the Company name and Select Private,and give the URL in the ‘Set up application properties’ page
– Select I have a Jira Key and paste the key in the field below the Server ID
– And fill in the following pages etc.

Migrating the database
Importing from older H2 data saved in xml backup (.zip)file
In Terminal:
Move the backup file to the import directory:
mv /var/atlassian/application-data/jira/export/HP_JIRA_Backup_1.zip /var/atlassian/application-data/jira/import/HP_JIRA_Backup_1.zip
In Jira site:
(SprocketWheelIcon)==>> System ==>> (Left menu)Restore System
Enter the filename(without path) of the backup(including the .zip extension)
Click Restore button.
IMPORTANT NOTE: This operation will overwrite all settings(except for the Database connection) which you already entered in the previous Setup Wizzard like your password/user/email/Language etc.. Therefore if this information are not exactly the same, you will have to logout and login again.

The Portfolio for Jira licence might have to be renewed at:
https://www.atlassian.com/purchase/cart

In order to correct the error, that will appear if the FLAG pool-test-while-idle is not set in the file dbconfig.xml, edit the file:
/var/atlassian/application-data/jira/dbconfig.xml and add the following line inside the settings block as follows:
<pool-test-while-idle>true</pool-test-while-idle>
eg.
<jdbc-datasource>
.....
<pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
<pool-test-while-idle>true</pool-test-while-idle>
<pool-test-on-borrow>false</pool-test-on-borrow>
.....
</jdbc-datasource>

Reason: The Set-up wizzard did not set it(inexistant) during its setting up the Database settings and the interface was complaining that it failed a database connection test. I had to make that change manually later and restart Jira.
Restart Jira and watch for errors.
service jira stop && service jira start && tail -f /var/atlassian/application-data/jira/log/atlassian-jira.log

Verifying the logs for errors:

You can check for errors via the Jira interface Log analyzer function in:
(SprocketWheelIcon)==>>System ==>>(Left menu)Support Tools==>>Log analyzer(TAB)==>>Refresh(middle right)

19 Dec 16 Display MySQL databases types in bash

Based on the site: http://stackoverflow.com/questions/213543/how-can-i-check-mysql-engine-type-for-a-specific-table

Here is a bash command that will display the databases types(innoDB or MyISAM) of all the MySQL databases except for the defaults ones(mysql, etc)
for i in $(mysql -u root -p -e "show databases;" | egrep -v 'Database|information_schema|mysql|performance_schema'); do echo "--------------------$i--------------------"; mysql -u root -p -e "use $i; show table status;"; done | awk '{print $1" "$2}' | column -t

You will need to type as many times as there are databases the MySQL root password.

Alternative:
Create a mysql user and use it’s password on the command line as follows:
for i in $(mysql -u <user> -p --password='secret' -e "show databases;" | egrep -v 'Database|mysql|performance_schema'); do echo "--------------------$i--------------------"; mysql -u <user> -p --password='secret' -e "use $i; show table status;"; done | awk '{print $1" "$2}' | column -t

30 Oct 16 Resolving Mysql error: Too many open files

Introduction:
As I upgraded from Mysql 5.5 to 5.6 suddenly some sites were showing the following error:
...... Too many open files
The issue has to do with the present limitations given to the system and PAM system to open max 1024 files. After doing some research I found this site below here which is in German in which the following is explained:
https://www.filewalker.de/ulimit-mysql-open_files_limit/

STEPS:
Check the files-open limits or running mysql server:
mysql -p -u root
mysql> SHOW VARIABLES LIKE 'open%';

The very possible output:
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 1024 |
+------------------+-------+
1 row in set (0.00 sec)

That means that Mysql server gets to open maximum 1024 which seems too little for Mysql 5.6.

Raising this limit
Edit the file /etc/security/limits.conf and add the following lines:
mysql hard nofile 65535
mysql soft nofile 65535

This will raise the limit of open files to 65535 for the user mysql only.
If you want to rise this limit for all users then replace the word mysql for *
eg.
* hard nofile 65535
* soft nofile 65535

And according to this site edit the file /etc/pam.d/common-session and add this line at the end:
session required pam_limits.so
Note: I’m not sure this step is really needed though. Some people did try without it and it also worked. For me, in Debian Wheezy, I had to do this otherwise I was still getting the error.

For systems that run systemd instead of InitV do the following:
Edit file /usr/lib/systemd/system/mysqld.service
Add these 2 lines at the end:
LimitNOFILE=65535
LimitNPROC=65535

Restart Mysql server and test it again
service mysql restart
mysql -p -u root
mysql>> SHOW VARIABLES LIKE 'open%';

The hopeful output:
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 65535 |
+------------------+-------+
1 row in set (0.00 sec)

This error should no more appear.

30 May 16 Resetting MariaDB root password in Ubuntu 16.04 LTS

Introduction:
Becasue it’s such a good article and don’t want to lose it, this following article is a full copy (with maybe some minor changes) taken from this site:
http://tecadmin.net/steps-to-reset-mariadb-root-password-in-linux/#

Step 1: Stop MariaDB Service
First we need to stop MariaDB service using following command.
# /etc/init.d/mysql stop
Shutting down MySQL. [ OK ]

Step 2: Start MariaDB in Safe Mode
Now connect to MariaDB in safe more using skip grant and run this command in background.
# mysqld_safe --skip-grant-tables &
.
[1] 6218
140118 22:27:09 mysqld_safe Logging to '/var/lib/mysql/localhost.localdomain.err'.
140118 22:27:09 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Step 3: Login to MariaDB and Change Password
After starting MariaDB in safe more connect to MariaDB and run following commands to change root password.
# mysql
.
MariaDB [(none)]> use mysql;
MariaDB [mysql]> UPDATE user SET password=PASSWORD("new_password") WHERE User='root';
MariaDB [mysql]> FLUSH PRIVILEGES;
MariaDB [mysql]> quit;

Step 4: Stop and Start MariaDB
After changing password, stop the MariaDB service and start it again in normal mode using following commands.
# kill $(ps aux | grep -v 'grep' | grep /usr/bin/mysqld_safe | awk '{print $2}')
# service mysql start

Step 5: Login to MariaDB using New Password
At this stage you have successfully updated you root MariaDB password, Lets connect to MariaDB using new password.
# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 1
Server version: 5.5.34-MariaDB MariaDB Server
.
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
.
MariaDB [(none)]>

RESULTS:
Well, after having done all of the above I could sign-up as root via the command line ‘mysql -u root -p’ but could not sign-up as root via PHPMyadmin.
So here is what I did:
Step 6: login as root using this command:
# mysql -u root -p
Step 7: Enter the following commands to create the new user ‘root2’ and set the same password as for ‘root’
MariaDB [(none)]> use mysql;
MariaDB [mysql]> CREATE USER 'root2'@'localhost' IDENTIFIED BY 'same_pass_as_root';
MariaDB [mysql]> GRANT ALL PRIVILEGES ON *.* TO 'root2'@'localhost' WITH GRANT OPTION;
MariaDB [mysql]> FLUSH PRIVILEGES;
MariaDB [mysql]> quit;

I could then login as ‘root2’ in PHPMyadmin and have all privileges as for root.
That works and don’t ask me why 😉

21 Jan 16 Create a new database and use in MySQL/MariaDB

These commands will create a new database in MySQL/MariaDB including a new user/password with full access to the DB.
mysql -p -u root
Enter the MySQL root password.
mysql> CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_bin;
mysql> GRANT ALL PRIVILEGES ON dbname.* TO 'user'@'localhost' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;
mysql> QUIT

27 Aug 15 Resetting MySQL passwords in Linux Debian/Ubuntu

In Linux Debian/Ubuntu an extra user debian-sys-maint which also has all the access rights is used for maintenance. It can also be used to reset users passwords especially the root password when lost. Here are some tips regarding resetting them.

Resetting the ‘root’ password
mysqladmin -u root -p password "MyNewPass"
OR
mysql -u debian-sys-maint -p
update mysql.user set Password=password('MyNewPass') where User='root';
flush privileges;
quit;

Resetting/creating the user and giving it all the access rights.
eg. debian-sys-maint
mysql -u root -p
create user 'debian-sys-maint'@'localhost' identified by 'UserPassword';
grant all privileges on *.* to 'debian-sys-maint'@'localhost'
flush privileges;
quit;

03 Aug 15 PAM-Mysql user authentication in Ubuntu 14.04 LTS Server

Introduction:

As I was wanting to set-up a cluster of web servers based on Apache2 and fcgi I realized that I didn’t want to have to create/delete/update each individual fcgi user in each web server. Therefore I decided to authenticate the fcgi users through MySQL (in fact MariaDB). I’ve done that many years back but this time it seemed very new to me anyway. I spent at least 3 days on it trying to follow some tutorials but I had to combine many of them to finally make it work. Here I don’t pretend to have found the best solution or method. So if any of you can improve on it, I would be very thankful for your contribution. So here it goes:

Configuring PAM with MySQL as authentication source instead of /etc/{passwd|shadow} on Ubuntu Server 14.04-2.
This tutorial is partly based on the following one. In fact I shortened the explanations, so for more explanations see this link:
http://www.spencerstirling.com/computergeek/mysqluser.html
and https://github.com/NigelCunningham/pam-MySQL
and http://serverfault.com/questions/608692/token-error-when-trying-to-change-password-through-pam-mysql

Install packages

Install the MariaDB server/client packages(Replacement to MySQL)
apt-get install mariadb-client mariadb-server libpam-mysql libnss-mysql-bg
Install phpmyadmin for comfortable viewing
apt-get install phpmyadmin
Install mcrypt PHP5 module
mkdir /usr/local/lib/libmcrypt
chown www-data: /usr/local/lib/libmcrypt
apt-get install mcrypt php5-mcrypt
php5enmod mcrypt
service apache2 restart

Configure the PAN auth user MariaDB Database

Start the mysql client(in fact MariaDB client but still called MySQL 😉 )
NOTE: Remember to change the {pam-user Password} to a proper password in these statement:
mysql -u root -p
Issue the following SQL commands:
create database nss_mysql;
quit;

Create an SQL file to beloaded in mysql later.
Note: The GRANT update line at the bottom. This is necessary in order for nss-shadow to be able to CHANGE anything. The nss user only has read privileges.
Filename: nss_mysql.sql
Content:
USE nss_mysql;
DROP TABLE IF EXISTS groups;
CREATE TABLE groups (
dbid int(11) NOT NULL auto_increment primary key,
name varchar(30) DEFAULT '' NOT NULL,
gid int(11) NOT NULL,
password varchar(64) DEFAULT 'x' NOT NULL,
flag char(1) DEFAULT 'A'
);
INSERT INTO groups VALUES ('users',100,'x','A');
DROP TABLE IF EXISTS users;
CREATE TABLE users (
dbid int(11) NOT NULL auto_increment primary key,
username varchar(50) DEFAULT '' NOT NULL,
gecos varchar(40) DEFAULT '' NOT NULL,
shell varchar(20) DEFAULT '/bin/sh' NOT NULL,
password varchar(60) DEFAULT '' NOT NULL,
flag char(1) DEFAULT 'N' NOT NULL,
uid int(11) NOT NULL,
gid int(11) NOT NULL,
homedir varchar(64) DEFAULT '/bin/sh' NOT NULL,
lstchg varchar(50) NOT NULL default '',
min int(11) NOT NULL default '0',
max int(11) NOT NULL default '0',
warn int(11) NOT NULL default '7',
inact int(11) NOT NULL default '-1',
expire int(11) NOT NULL default '-1'
);
DROP TABLE IF EXISTS grouplist;
CREATE TABLE grouplist (
dbid int(11) NOT NULL auto_increment primary key,
gid int(11) DEFAULT '0' NOT NULL,
uid int(11) DEFAULT '0' NOT NULL,
username varchar(50) DEFAULT '' NOT NULL
);
GRANT select(dbid,username,uid,gid,gecos,shell,homedir,flag) on users to 'nss'@localhost identified by 'ieopurASDF';
GRANT select(dbid,name,gid,password,flag) on groups to 'nss'@localhost identified by 'ieopurASDF';
GRANT select(dbid,gid,uid,username) on grouplist to 'nss'@localhost identified by 'ieopurASDF';
GRANT select(dbid,username,password,uid,gid,gecos,shell,homedir,flag,lstchg,min,max,warn,inact,expire) on users to 'nss'@localhost identified by 'ieopurASDF';
GRANT select(dbid,name,gid,password,flag) on groups to nss@localhost identified by 'ieopurASDF';
GRANT select(dbid,gid,uid,username) on grouplist to nss@localhost identified by 'ieopurASDF';
GRANT select(dbid,username,uid,gid,gecos,shell,homedir,flag) on users to 'nss-shadow'@localhost identified by 'ruASDFDER';
GRANT select(dbid,name,gid,password,flag) on groups to 'nss-shadow'@localhost identified by 'ruASDFDER';
GRANT select(dbid,gid,uid,username) on grouplist to 'nss-shadow'@localhost identified by 'ruASDFDER';
GRANT select(dbid,name,gid,password,flag) on groups to nss-shadow@localhost identified by 'ruASDFDER';
GRANT select(dbid,gid,uid,username) on grouplist to nss-shadow@localhost identified by 'ruASDFDER';
GRANT select(dbid,username,password,uid,gid,gecos,shell,homedir,flag,lstchg,min,max,warn,inact,expire) on users to 'nss-shadow'@localhost identified by 'ruASDFDER';
GRANT update(dbid,username,password,uid,gid,gecos,shell,homedir,flag,lstchg,min,max,warn,inact,expire) on users to 'nss-shadow'@localhost identified by 'ruASDFDER';
FLUSH PRIVILEGES;

Configuring the NSS (Linux Authorization system)

These above SQL DB structures and tables are based on the following Ububtu 14.04-2 LTS Server content of /etc/libnss-mysql.conf which listed here:
getpwnam SELECT username,'x',uid,gid,gecos,homedir,shell \
FROM users \
WHERE username='%1$s' \
LIMIT 1
getpwuid SELECT username,'x',uid,gid,gecos,homedir,shell \
FROM users \
WHERE uid='%1$u' \
LIMIT 1
getspnam SELECT username,password,lstchg,min,max,warn,inact,expire,flag \
FROM users \
WHERE username='%1$s' \
LIMIT 1
getpwent SELECT username,'x',uid,gid,gecos,homedir,shell \
FROM users
getspent SELECT username,password,lstchg,min,max,warn,inact,expire,flag \
FROM users
getgrnam SELECT name,password,gid \
FROM groups \
WHERE name='%1$s' \
LIMIT 1
getgrgid SELECT name,password,gid \
FROM groups \
WHERE gid='%1$u' \
LIMIT 1
getgrent SELECT name,password,gid \
FROM groups
memsbygid SELECT username \
FROM grouplist \
WHERE gid='%1$u'
gidsbymem SELECT gid \
FROM grouplist \
WHERE username='%1$s'

Edit /etc/nsswitch.conf and change the lines:
passwd: compat
group: compat
shadow: compat
TO:
passwd: compat mysql
group: compat mysql
shadow: compat mysql

This will tell NSS to look for the user information first in files (/etc/passwd & /etc/shadow) and if not found in these files then look in the mySQL database.
Edit the file /etc/libnss-mysql.conf and replace the proper user and password you had defined for the ‘nss’ user in the SQL file above.
Example:
Refering to the user and password in the SQL file above: …nss@localhost identified by ‘ieopurASDF’…
Change the file /etc/libnss-mysql.conf,from:
database auth
username nss-user
password userpass
TO
database nss_mysql
username nss
password ieopurASDF

Edit the file /etc/libnss-mysql-root.conf and replace the proper user and password you had defined for the nss-shadow user in the SQL file above.
Example:
Refering to the user and password in the SQL file above: …nss-shadow@localhost identified by ‘ruASDFDER’…
Change the file /etc/libnss-mysql-root.conf,from:
username nss-root
password rootpass
TO
username nss-shadow
password ruASDFDER

Change the access rights of the file /etc/libnss-mysql-root.conf
chmod 600 /etc/libnss-mysql-root.conf

Configuring the PAM Modules

Change to the directory /etc/pam.d/ where the PAM modules configurations are found.
cd /etc/pam.d
Rename the originals to keep them in case:
mv common-account common-account.orig ; touch common-account
mv common-auth common-auth.orig ; touch common-auth
mv common-session common-session.orig ; touch common-session
mv common-password common-password.orig ; touch common-password

Make common-account look like this:
account sufficient pam_mysql.so config_file=/etc/pam-mysql.conf
account required pam_unix.so

Make common-session look like this:
session sufficient pam_mysql.so config_file=/etc/pam-mysql.conf
session required pam_unix.so

Make common-auth look like this:
auth sufficient pam_mysql.so config_file=/etc/pam-mysql-shadow.conf
auth required pam_unix.so nullok_secure

Make common-password look like this:
password sufficient pam_mysql.so config_file=/etc/pam-mysql-shadow.conf
password required pam_unix.so nullok obscure sha512

Now make all of these only root readable:
chmod 600 common-*
Try to SSH in as a both a regular user AND as root – make sure that it all works.

YOU’RE NOT DONE YET!!! If you are running either an IMAP or POP3 server then (unfortunately) these “common-*”
files are not used by default. You can easily use them by (for example) changing your /etc/pam.d/imap file to look like:
@include common-auth
@include common-account
@include common-session
@include common-password

Look around in /etc/pam.d/ for any other services that may not be using common-* and update them appropriately.

PAM-mysql configuration files

We rename the already existing PAM-mysql configuration file:
mv /etc/pam-mysql.conf /etc/pam-mysql.conf.orig
Then we create the following configuration files: /etc/pam-mysql.conf and /etc/pam-mysql-shadow.conf
touch /etc/pam-mysql.conf
Content:
users.host = localhost
users.database = nss_mysql
users.db_user = nss
users.db_passwd = ieopurASDF
users.table = users
users.user_column = username
users.password_column = password
users.password_crypt = 3

Create the file /etc/pam-mysql-shadow.conf
touch /etc/pam-mysql-shadow.conf
Content:
users.host = localhost
users.database = nss_mysql
users.db_user = nss-shadow
users.db_passwd = ruASDFDER
users.table = users
users.user_column = username
users.password_column = password
users.password_crypt = 3

Steps to add the first users:(For testing purposes)

Now that you have your database functioning properly (with the special MySQL modules for NSS and PAM) it’s time to actually add a user. The steps I’ll outline here are very MANUAL.
mysql -u root -p
and execute the command:
select * from nss_mysql.users;
That will list the existing users. In our case no users exist yet, so we’ll use user_id=group_id=100 (internal MySQL designation) and uid=gid=1000 (system sees this). Put these relevant MySQL commands into a file – say addusers.sql:
INSERT INTO nss_mysql.groups VALUES (0,'spencer',1000,'x','A');
INSERT INTO nss_mysql.users VALUES (0,'spencer','Spencer Stirling','/bin/bash','','A',1000,1000,'/home/spencer','','0','0','7','-1','-1');
INSERT INTO nss_mysql.grouplist VALUES (0,1000,1000,'spencer');
INSERT INTO nss_mysql.grouplist VALUES (0,100,1000,'spencer');

INSERT INTO nss_mysql.groups VALUES (0,'sally',1001,'x','A');
INSERT INTO nss_mysql.users VALUES (0,'sally','Sally Smith','/bin/bash','','A',1001,1001,'/home/sally','','0','0','7','-1','-1');
INSERT INTO nss_mysql.grouplist VALUES (0,1001,1001,'sally');
INSERT INTO nss_mysql.grouplist VALUES (0,100,1001,'sally');

INSERT INTO nss_mysql.groups VALUES (0,'christine',1002,'x','A');
INSERT INTO nss_mysql.users VALUES (0,'christine','Christine Weber','/bin/bash','','A',1002,1002,'/home/christine','','0','0','7','-1','-1');
INSERT INTO nss_mysql.grouplist VALUES (0,1002,1002,'christine');
INSERT INTO nss_mysql.grouplist VALUES (0,100,1002,'christine');

Obviously the first user creation above will create:
a “spencer” group with gid=1000, then it will create a “spencer” user with uid=1000.
Then, the “spencer” user will be put into the “spencer” group.
You should increment them with the next user (i.e. 1000 —> 1001 ect.).
The last statement also adds the user “spencer” to the group “users”(gid 100) (created in the beginning).
I assigned dummy ID dbid‘ to the tables only to create unique table columns to be able to manually change the tables and values from phpmadmin.
Now run these statements with:
mysql -u root -p < adduser.sql
That will add your user (with a blank password, by the way).
You’re almost done! Now it’s time to actually create the user’s home directory. That part is very easy:
cp -ax /etc/skel /home/spencer
chown -R spencer:spencer /home/spencer

Installing pure-ftpd for pam-mysql

apt-get install pure-ftpd
The following configuration of PureFTPD will provide FTP/ and FTPS with jailed Users.
Edit the file /etc/pam.d/pure-ftpd as follows:
auth sufficient pam_mysql.so config_file=/etc/pam-mysql-shadow.conf sense=deny file=/etc/ftpusers onerr=succeed
# Uncomment next line to allow non-anonymous ftp access ONLY for users,
# listed in /etc/ftpallow
#auth required pam_listfile.so item=user sense=allow file=/etc/ftpallow onerr=fail
# Standard pam includes
@include common-account
@include common-session
@include common-auth
auth required pam_shells.so

For configuring PureFTPD, run the following commands:
echo '20000 20099' > /etc/pure-ftpd/conf/PassivePortRange
echo "yes" > /etc/pure-ftpd/conf/NoAnonymous
echo "yes" > /etc/pure-ftpd/conf/ChrootEveryone
echo "yes" > /etc/pure-ftpd/conf/IPV4Only
echo "1" > /etc/pure-ftpd/conf/TLS

Fro cresating a TLS self-signed certificate fro ProFTPD run the following commands and enter the appropriate information:
mkdir -p /etc/ssl/private/
openssl req -x509 -nodes -days 97300 -newkey rsa:2048 -keyout /etc/ssl/private/pure-ftpd.pem -out /etc/ssl/private/pure-ftpd.pem
chmod 600 /etc/ssl/private/pure-ftpd.pem
service pure-ftpd restart

More configuration for mail services

Hopefully you have everything in “/etc/skel” set up appropriately. Mail is probably the most important thing here. For example, I set up a skeleton mail and spam folder with the command:
maildirmake /etc/skel/Maildir
maildirmake /etc/skel/Maildir/.Spam

In the “/etc/skel” directory I am ALSO sure to have an appropriate “.forward” to forward all of that spam into the right folder,
and I put a barebones “.muttrc” file in there, too, so that my users can hit the ground running with Mutt.
Please see my Email Howto for more info concerning these concepts.
Don’t forget to add email ALIASES to Exim4 if necessary for your new user!!!
To delete a user (and that user’s GROUP!!!) try these MySQL commands:
DELETE FROM nss_mysql.users where username='spencer';
DELETE FROM nss_mysql.groups where name='spencer';
DELETE FROM nss_mysql.grouplist where uid='1000'

Then you can delete the user’s home directory (although you should give some leniency there!!!).
NOTE: The system commnad useradd and passwd will continue to manage the /etc/passwd and /etc/shadow
The new scripts to create and change the user’s password are below:
myadduser.sh and mypasswd.sh

BASH SCRIPTS

Necessary mysql root password(Replace the $password with a proper mysql root password)
echo "$password" > $PWD/sqlrootpw.txt
chmod 600 $PWD/sqlrootpw.txt
touch $PWD/myadduser.sh
chmod 755 $PWD/myadduser.sh

Script for adding a user

myadduser.sh
Syntax: myadduser.sh username 'realname' shell 'password' homedir
Content:
#!/bin/bash
# Purpose: adds a user in mysqldatabase for PAM authentication/authorization
# syntax: myadduser.sh # Note: each new user will be part of the users group as well as the group bearing username
# which also will have the same GID as the UID
# It will not take note of deleted users therefore not use their free UID
# UID and GID will
# Dependencies: mySQL root password saved in $rootpwfile which should be 'chmod 600'
# ------------------------------------------------------
# Check the syntax
if [ $# -ne 5 ]; then
echo "ERROR: Wrong syntax"
echo "Usage: myadduser.sh username 'realname' shell 'password' homedir"
exit 1
fi
#
# Constants
#--------------
rootpwfile=$PWD/sqlrootpw.txt
# Get MySQL root PW
rootpw=$(head -n1 $rootpwfile | awk '{print $1}')
#
# Get the username
username=$1
realname=$2
shell=$3
password=$4
homedir=$5
#
#------- Start ---------
# First we find out the highest UID of all users
lastUID=$(echo 'select * from nss_mysql.users;' | mysql -N -u root --password="$rootpw" | rev | awk '{print $8}' | rev | sort -n | tail -n1)
# set it to 999 if no users entered yet
if [ -z $lastUID ]; then
lastUID="999"
fi
#
# Calculate the next UID and DB:user_id
newUID=$[ $lastUID + 1 ]
# Check if the user exists in the /etc/passwd increment the UID until not found
while (grep -q ":x:${newUID}:" /etc/passwd) ; do
newUID=$[ $newUID + 1 ]
done
newGID=$newUID
#
# Find out the encryption method used in this system. Not used since it's SHA512.
#crypt_method=$(grep "^ENCRYPT_METHOD" /etc/login.defs | awk '{ print $2}')
#
# Encrypt the password .... hummm tried everything but authentication is not working with SHA256/SHA512. Decided for MD5.
crypt_method='MD5'
crypt=$(echo "${username}:${password}" | chpasswd -S -c $crypt_method | cut -d: -f2)
#
# Enter the data into the Database
echo "INSERT INTO nss_mysql.groups VALUES (0,'$username',$newUID,'x','A');" | mysql -u root --password="$rootpw"
echo "INSERT INTO nss_mysql.users VALUES (0,'$username','$realname','$shell','$crypt','A',$newUID,$newGID,'$homedir','','0','0','7','-1','-1');" | mysql -u root --password="$rootpw"
echo "INSERT INTO nss_mysql.grouplist VALUES (0,$newGID,$newUID,'$username');" | mysql -u root --password="$rootpw"
echo "INSERT INTO nss_mysql.grouplist VALUES (0,100,$newUID,'$username');" | mysql -u root --password="$rootpw"
#
# Create the home directory if not existing
if ! [ -d $homedir ]; then
echo "Creating the home directory: $homedir"
cp -ax /etc/skel $homedir
chown -R ${username}:${username} $homedir
else
echo "Home directory $homedir already exists. Doing nothing"
fi
#
# Display the result o the new user's DB record
echo "########## Result ##########"
mysql -u root --password="$rootpw" -vvv -e "select * from nss_mysql.users where uid='$newUID';"
ls -ld $homedir
#
#eof

mydeluser.sh
#!/bin/bash
# Purpose: Change the password of an existing user in the MySQL database for a PAM-mysql environment.
# Syntax: mypasswd.sh username -u username [-r]
# Dependencies: mySQL root password saved in $rootpwfile which should be 'chmod 600'
# --------------------------------------------------------
# Check the syntax
function usage () {
echo "ERROR: Wrong syntax"
echo "Usage: mypasswd.sh -u username [-r]"
echo "use '-r' option to delete the user's home directory as well"
exit 1
}
#
# Constants
#--------------
rootpwfile=$PWD/sqlrootpw.txt
# Get MySQL root PW
rootpw=$(head -n1 $rootpwfile | awk '{print $1}')
#
# get the options
deldir=false
while getopts "u:r" OPTION
do
case $OPTION in
u) user=$OPTARG
;;
r) deldir="true"
;;
h|?|*)
usage
;;
esac
done
#
# Get the UID of the user
homedir=$(echo "select homedir from nss_mysql.users where username='$user';" | mysql -u root --password="$rootpw" | tail -n1)
#
# Check if the user exists
if ! [ -z $homedir ]; then
# Delete the user's DB records
echo "Deleting the user from DB"
echo "DELETE FROM nss_mysql.users where username='$user';" | mysql -u root --password="$rootpw"
echo "DELETE FROM nss_mysql.groups where name='$user';" | mysql -u root --password="$rootpw"
echo "DELETE FROM nss_mysql.grouplist where username='$user';" | mysql -u root --password="$rootpw"
# Delete the home directory -r option used.
if [ $deldir = "true" ]; then
echo "Deleting the user home directory '$homedir'"
rm -rf $homedir
fi
else
echo "ERROR: User $user not found in database: nss_mysql.users"
fi
#
# eof

mypasswd.sh
#!/bin/bash
# Purpose: Change the password of an existing user in the MySQL database for a PAM-mysql environment.
# Syntax: mypasswd.sh -u username -p ['passowrd'] -r
# Dependencies: mySQL root password saved in $rootpwfile which should be 'chmod 600'
# --------------------------------------------------------
# Constants
#--------------
rootpwfile=$PWD/sqlrootpw.txt
# Get MySQL root PW
rootpw=$(head -n1 $rootpwfile | awk '{print $1}')
#
# Check the syntax
function usage () {
echo "ERROR: Wrong syntax"
echo "Usage: mypasswd.sh -u username -p ['passowrd'] -r"
echo "use -r if the user's home directory needs to be deleted as well"
exit 1
}
#
# get the options
deldir=false
while getopts "u:p:" OPTION
do
case $OPTION in
u) user=$OPTARG
;;
p) password=$OPTARG
;;
h|?|*)
usage
;;
esac
done
#
# Check if the user exists
dbuser=$(echo "select username from nss_mysql.users where username='$user';" | mysql -u root --password="$rootpw" | tail -n1)
if ! [ -z $dbuser ]; then
# Encrypt the password .... hummm tried everything but authentication is not working with SHA256/SHA512. Decided for MD5.
crypt_method='MD5'
crypt=$(echo "${user}:${password}" | chpasswd -S -c $crypt_method | cut -d: -f2)
# change the password
echo "UPDATE nss_mysql.users SET password='$crypt' WHERE username='$user';" | mysql -u root --password="$rootpw"
else
echo "ERROR: User $user not found in database: nss_mysql.users"
fi
#
# eof

Troubleshooting libpam-mysql

Situation: As I was writing this tutorial I tried to login as a user to the system with ssh and it worked. Then a few months later after a few system updates it didn’t work. Here is the error message I was seeing in the /var/log/auth.log file:
pam_mysql - non-crypt()ish MD5 hash is not supported in this build.
I looked all over the place in Internet and found the only answer to it in here:
http://osdir.com/ml/ubuntu-bugs/2014-07/msg10370.html
Relevant Content:
1, sudo apt-get install libmysqlclient-dev libpam-dev libssl-dev
2, ln -s /usr/include/openssl/md5.h /usr/include/md5.h
3, Makefile.in#109: DEFS = @DEFS@ -I. -I$(srcdir) -I. -DHAVE_OPENSSL
4, ./configure --prefix=/usr --with-openssl | grep md5

So here is the more detailed version of this patching procedure:
apt-get install libmysqlclient-dev libpam-dev libssl-dev libnss-mysql-bg libpam-mysql
apt-get source pam-mysql
apt-get build-dep pam-mysql
ln -s /usr/include/openssl/md5.h /usr/include/md5.h

Edit the file Makefile.in and make sure the line #109 has the following content:
DEFS = @DEFS@ -I. -I$(srcdir) -I. -DHAVE_OPENSSL
Start the compiling pam-mysql:
./configure --prefix=/usr --with-openssl | grep md5
make

Create the Debian package this time called pam-mysql:
checkinstall --install=no
Give it a slightly different version number/name and create the package
eg. 0.7~RC1-MD5
– Deinstall the original package libpam-mysql:
apt-get remove libpam-mysql
– Install the newly compiled package:
dpkg install pam-mysql_0.7~RC1-MD5-1_amd64.deb

Now in the above script (mynewuser.sh) the user’s data should be written as follows:
Write the password in the database via the normal MySQL MD5 function. eg.
echo "INSERT INTO ${DB}.users VALUES (0,'$username','$realname','$shell','tempPW','A',$newUID,$newGID,'$ftphtdocs','','0','0','7','-1','-1');" | mysql --host=$DBHOST -u root --password="$rootpw"
echo "UPDATE ${DB}.users SET password = MD5('$password') WHERE uid = $newUID;" | mysql --host=$DBHOST -u root --password="$rootpw"

10 Oct 13 Rebuild MySQL replication slave LIVE from master

Problem:
You have 2 MySQL servers in the configuration Master ==>> Slave and the slave has gone very badly out of sync with the master. Normally it’s no real problem if you can lock the master tables, make a dump of the databases of the master and import them back into the slave. But the real problem here is that you cannot afford to stop the applications writing on the master MySQL databases while you are rebuilding the slave and putting it back in sync with the master.

Possible solution: Rebuild LIVE the slave from the master’s databases without disturbing the master at all and still be in sync at the end. You might think that while you run the mysqldump, new data will be written to the databases in the master, and the mysqldump might not see them. Or after the mysqldump has finished running, new data might be written to the master, then how will the slave get synchronized with it as well at the end. Well with all the multiple options given to the mysqldump and the way the slave is restarted it gets all solved in this manner:
– Make a list of databases to rebuild to be fed to the mysqldump with only the DBs that need it(no mysql or information_shema databases)
– The databases dump starts and concurrently remembers what it is still receiving from applications.
– At the end of the dump it also dumps this data received in the meantime.
– Then it immediately flushes his binlog. This way the new (unrecorded data in dump file) is getting written to the new binlog file.
– The dump file gets imported into the slave mySQL server, deleting every database before importing the new data.
– At the end of the import action, before we start the replication process, we instruct the slave mysql server to point itself at pointer position:4(normal master binlog pointer start position) of the continuously growing master binlog.
– Then we instruct the slave to start the replication as slave.

No data is lost, the master never got disturbed while doing this and the slave is in sync with the master again. In order to do all this without making mistakes, I created a script which does the work while I’m having a cup of coffee and maybe two.

Important note: The configuration ‘master replicated to slave’ is not a very stable one concerning keeping the replication in sync. If the slave goes down and restarts , all is fine, but if the master goes down and restarts then very often the slave is no more in sync.
My experience is that, when only 2 servers(master and slave) are involved in a replication system, here is my recommendation which offers a much more stable replication system:
You do ‘loop replication’ between them.
In other words, every server is a master and a slave of the other.
It is explained in details in this article: //tipstricks.itmatrix.eu/?p=1320

Note 2:
This script below uses 2 methods of dumping and importing in the data. One uses the ‘pipe’ method where no temporary dump file is created. The dump data in immediately fed into the slave via the pipe. This method has the advantage of being fast and not requiring to create a large temporary dump file. The disadvantage is that, with some very big databases the process got often broken. I didn’t try it too many times with big(2-12GB) since one waits a long time to see a failure. For this reason I implemented the temporary dump file method. In this method the dump file is created locally in the master, then carried to the slave at the same path and then imported in the slave MySQL server. If this ‘dump file’ method is used you will need to run this script on the master.

Note 3:
In this following script the databases ‘information_schema‘ and ‘mysql‘ are excluded from being rebuilt. In case one of them is corrupt then you need to take them out of the EXCEPTIONS list at the beginning of the script. See the original information site on mysqldump at: https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

If needed adapt the temporary dump file path in the script if needed.

So here is the bash script that does it all:
#!/bin/bash
# Name: MySQL_REBUILD_LIVE_SLAVE_from_MASTER
# Purpose: Rebuilds live all Databases of a replication SLAVE based on a good running MASTER
# This rebuilt action is done without stopping the MASTER MySQL server or even locking the tables
# or the destination server (SLAVE)
# Syntax: MySQL_REBUILD_LIVE_SLAVE_from_MASTER Master:Port Slave:Port {F|P}
# eg. MySQL_REBUILD_LIVE_SLAVE_from_MASTER db1:3306 www1:3306 P
# {F|P} - Dump method F) Uses a full temporary dump file method P) Uses the pipe method
# Note: In some situation where the databases are very big (GB) the Pipe (P) method might not work well
# In these case it is recommended to use the File(F) method
# Changes: 05 Dec 2008 First implementation of script
# 10 Oct 2013 Added the File(F) transfer method, added --skip-lock-tables to the dump command, added the manual entry of root PW
# Notes 1: In the case of using the Dump file method, the file will be transferred via rsync to the slave for importing.
# Therefore the key of the user running the script must be already installed in slave before running the script
#-----------------------------------------------------------------------------------
# Some constants:
DUMPFILE="/tmp/mydump.sql"
# Resync the databases except the following Databases
EXCEPTIONS="information_schema mysql test"
#
# Functions
# Syntax: testhost addr port. Returns: hostOK=1
testhost () {
hostOK=0
if (nmap -n --host_timeout 1600 --max_rtt_timeout 1600 -p $2 -P0 $1 2>/dev/null | grep -q "open" &>/dev/null); then
hostOK=1
fi
};
#
usage () {
echo "ERROR: Somethig is wrong with the given arguments"
echo "Syntax: MySQL_REBUILD_LIVE_SLAVE_from_MASTER Master:Port Slave:port {F|P}"
echo " eg. MySQL_REBUILD_LIVE_SLAVE_from_MASTER master1:3306 slave1:3306 P"
exit 1
}
#
# Check the command syntax
if [ $# -ne 3 ]; then
usage
fi
#
# Get the mysql root password
read -s -p “Please enter the MySQL root password: ” rootpw
#
#- Check the hosts info validity
if ! (echo $1 | grep ':'); then
echo "ERROR: The 2nd parameter(master) must be the combination 'host:port'"
exit 3
fi
#
if ! (echo $2 | grep ':'); then
echo "ERROR: The third parameter must be the combination 'host:port'"
exit 4
fi
#
method=$3
#
# Check the hosts connectivity of master host
Mhost=$(echo $1 | cut -d: -f1)
Mport=$(echo $1 | cut -d: -f2)
#
testhost $Mhost $Mport
if [ $hostOK = "0" ]; then
echo "ERROR: The master $Mhost:$Mport does not respond"
exit 5
fi
#
# Check the hosts connectivity of slave host
#
Shost=$(echo $2 | cut -d: -f1)
Sport=$(echo $2 | cut -d: -f2)
#
testhost $Shost $Sport
if [ $hostOK = "0" ]; then
echo "ERROR: The slave $Shost:$Sport does not respond"
exit 6
fi
#
# Stop and reset the slave
echo "STOP SLAVE; RESET SLAVE;" | mysql -h $Shost --port=$Sport -u root --password=$rootpw
#
databases=""
for DB in $(echo "show databases;" | mysql -h $Shost --port=$Sport -u root --password=$rootpw | grep -v Database) ; do
# Only delete/add the databases that are not in the Exceptions list
if ! (echo $EXCEPTIONS | grep -q $DB); then
# here I was deleting the databases one by one before recreating them in slave
# I replaced this by the option --add-drop-database in mysqldump
#echo "Deleting database $DB on Slave $Shost:$Sport"
#echo "DROP DATABASE $DB;" | mysql -h $Shost --port=$Sport -u root --password=$rootpw
# Corrected. Thanks to Dorian Kavaja
if [ "$databases" == "" ]; then
databases=$DB
else
databases=$databases’ ‘$DB
fi
fi
done
# The following lines is thanks to Dorian Kavaja
#because sometimes the log file on the master was changed before the dump was finished
echo “FLUSH LOGS;” | mysql –host=$Mhost –port=$Mport -u root –password=$rootpw
echo “RESET MASTER;” | mysql –host=$Mhost –port=$Mport -u root –password=$rootpw
#
# Now do the transfer
# Added “–routines –triggers” to the mysqldump command, so triggers and functions were included in the dump.
# Thanks to Dorian Kavaja
case $method in
P) # Transfer all databases from master to slave directly using a pipe(P)
echo "Transfering the all databases from master $Mhost:$Mport into slave $Shost:$Sport directly"
mysqldump -h $Mhost --port=$Mport -u root --password=$rootpw \
--single-transaction --flush-logs --master-data=2 --skip-lock-tables \
--add-drop-database --delete-master-logs --hex-blob –-routines –-triggers --databases $databases \
| mysql -h $Shost --port=$Sport -u root --password=$rootpw
;;
#
F) # Transfer the databases using a dump file
echo "Dumping the all databases from master $Mhost:$Mport into file $DUMPFILE"
mysqldump -h $Mhost --port=$Mport -u root --password=$rootpw \
--single-transaction --flush-logs --master-data=2 --skip-lock-tables \
--add-drop-database --delete-master-logs --hex-blob –-routines –-triggers --databases $databases > $DUMPFILE
#
echo "Transferring the dump file $DUMPFILE from Master $Mhost to slave $Shost via compressed rsync"
rsync -vz $DUMPFILE $Shost:$DMPFILE
echo "Importing the dump file ($DUMPFILE) into slave MySQL server $Shost"
ssh $Shost "mysql -h $Shost --port=$Sport -u root --password=$rootpw < $DUMPFILE"
;;
#
*) usage ;;
esac
#
# Find out the master binlog file name
masterlogfile=$(echo "SHOW MASTER STATUS\G;" | mysql --host=$Mhost --port=$Mport -u root --password=$rootpw | grep "File:" | cut -d: -f2 | cut -d" " -f2)
#
# Sync the slave with master binlog position 4
echo "CHANGE MASTER TO master_log_file='$masterlogfile',master_log_pos=4;" | mysql -h $Shost --port=$Sport -u root --password=$rootpw
#
# Start the replication on slave
echo "START SLAVE;" | mysql -h $Shost --port=$Sport -u root --password=$rootpw
sleep 3
#
# Show slave status to see if all is in sync
echo "SHOW SLAVE STATUS \G;" | mysql -h $Shost --port=$Sport -u root --password=$rootpw

Extra Note: In order to avoid a stop of replication when duplicate entries error ocurs, it was suggested to add the following configuration in /etc/mysql/my.cfg
slave-skip-errors=1045,126,1062,1580

Happy replication resycing.

17 Jan 13 logrotate /usr/bin/mysqladmin: refresh failed; error: ‘Unknown error’

Problem:
After a fresh install of Debian Squeeze and mysql server, started to appear the following error as email from cron:

/etc/cron.daily/logrotate:
/usr/bin/mysqladmin: refresh failed; error: 'Unknown error'
error: error running shared postrotate script for '/var/log/mysql.log /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log '
run-parts: /etc/cron.daily/logrotate exited with return code 1

Cause:
After the installation of mysql-server package, the first time the mysql-server starts, the mysqld_safe creates an error logfile(/var/log/mysql/error.log) owned by root which prevents mysqladmin called by logrotate from flushing the log file, resulting in this error email from cron.

Solution:
Well some developer has made some patch in the mysql_safe but only for mysql 5.5 which is not yet used in Debian Squeeze.
So a quick solution would be to re-own the log file to mysql with the following command.
Note dependent on your mysql-server configuration (/etc/mysql/my.cf) just make sure the path to this error log is adapted to your configuration:
chown mysql: /var/log/mysql/error.log
The problem will go away.

Note:
I think the best solution would be to patch the script mysql_safe, but so far I didn’t have time to get into this research.
If any of you has done the work, please contribute by adding your solution as comment here. Thanks

03 Jan 13 Mysql Loop replicating in Debian Squeeze

I have been doing replicating from master to Slave for quite a while and sometimes found it a bit limited especially when the master in down and ‘writes’ need to be done to the databases. Here is a method where no matter which server is down you can always write to it and the changes will get replicated to the other one. Even if the other server is down, the changes will get synchronized automatically when it comes back up.
Note: This method only works between 2 MySQL servers where each server is the master and the slave of the other.
This method has proven very stable no matter which server is brought down and back up, the replication has always worked well.
Very useful to make backups of all databases on both servers without any service downtime.

Here I will name the 2 servers: A-server(192.168.0.84) and B-server(192.168.0.85).
Install standard Mysql server in both servers:
apt-get install mysql-server mysql-client
Create a replication user replicant and add a password for it.
Make sure the name given (here is replicant given)and passwords are the same on both servers.
This will be used to allow loop replication between the 2 servers.
In those commands below only the replication privileges are given to the replication user.
mysql -uroot -p
(enter the MySQL root pawssowrd)
mysql> CREATE USER 'replicant'@'%' IDENTIFIED BY 'ChangeMe';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicant'@'%';
mysql> GRANT REPLICATION CLIENT ON *.* TO 'replicant'@'%';
mysql> GRANT SUPER ON *.* TO 'replicant'@'%';
mysql> FLUSH PRIVILEGES;
mysql> quit;

In A-server:/etc/mysql/my.cnf
After the ‘Login and Replication’ section add the following lines:

#------------------------------- MASTER area ---------
server-id = 84
# ------------------------------ SLAVE area ----------
slave-skip-errors=1045,126,1062,1242
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
# ----------------------------------------------------
log-bin = /var/log/mysql/mysql-bin.log
log-error = /var/log/mysql/mysql-bin.err
log-bin-index = /var/log/mysql/log-bin.index

In B-server:/etc/mysql/my.cnf
After the ‘Login and Replication’ section add the following lines:

#------------------------------- MASTER area ---------
server-id = 85
# ------------------------------ SLAVE area ----------
# Preventing Replica-stop when duplicates are replicated
slave-skip-errors=1045,126,1062,1242
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
# ----------------------------------------------------
log-bin = /var/log/mysql/mysql-bin.log
log-error = /var/log/mysql/mysql-bin.err
log-bin-index = /var/log/mysql/log-bin.index

Start both MySql servers and issue the following command to see if the replication is already in sync.
It might be helpful beforehand to issue a few SQL writes commands to one of the servers to see if the replication does work.


echo "SHOW SLAVE STATUS\G;" | mysql --host=192.168.0.84 --port=3306 -u replicant --password=ChangeMe | egrep "Slave_IO_State:" | head -n 1 | cut -d: -f2
echo "SHOW SLAVE STATUS\G;" | mysql --host=192.168.0.85 --port=3306 -u replicant --password=ChangeMe | egrep "Slave_IO_State:" | head -n 1 | cut -d: -f2

If all is good then both results should be: Waiting for master to send event
If you want to see the whole information regarding the replication, issue the following command:
echo "SHOW SLAVE STATUS\G;" | mysql --host=192.168.0.84 --port=3306 -u replicant --password=ChangeMe
echo "SHOW SLAVE STATUS\G;" | mysql --host=192.168.0.85 --port=3306 -u replicant --password=ChangeMe

The replication is doing good when the following information in the results is found:
Waiting for master to send event
Read_Master_Log_Pos should be the same value as for Exec_Master_Log_Pos
Example:
Read_Master_Log_Pos: 106
Exec_Master_Log_Pos: 106

That should imply as well the following result:
Seconds_Behind_Master: 0