debian 9 – install mariadb 10.x

# apt-get install software-properties-common
# apt-get install dirmngr
# apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xF1656F24C74CD1D8
# add-apt-repository 'deb [arch=amd64] http://www.ftp.saix.net/DB/mariadb/repo/10.1/debian stretch main'
# apt-get update
# apt-get install mariadb-server
# systemctl start mariadb
# systemctl enable mariadb
# systemctl status mariadb
Advertisements

mysql – master to master replication

https://www.digitalocean.com/community/tutorials/how-to-set-up-mysql-master-master-replication

“MySQL replication is the process by which a single data set, stored in a MySQL database, will be live-copied to a second server. This configuration, called “master-slave” replication, is a typical setup. Our setup will be better than that, because master-master replication allows data to be copied from either server to the other one. This subtle but important difference allows us to perform mysql read or writes from either server. This configuration adds redundancy and increases efficiency when dealing with accessing the data.”

mysql01: 192.168.2.36

$ echo "192.168.2.36	mysql01" >> /etc/hosts
$ echo "192.168.2.37	mysql02" >> /etc/hosts
$ sudo apt-get install mysql-server mysql-client -y
$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

#bind-address = mysql01
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = example
#binlog_ignore_db = include_database_name

$ mysql -u root -p

mysql> create user 'replicator'@'%' identified by 'password';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'replicator'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 | example      |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# get the log file and log post in server2

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_host='mysql02', master_user='replicator', master_password='password', master_log_file='mysql-bin.000003',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql02: 192.168.2.37

$ echo "192.168.2.36	mysql01" >> /etc/hosts
$ echo "192.168.2.37	mysql02" >> /etc/hosts
$ sudo apt-get install mysql-server mysql-client -y
$ sudo nano /etc/mysql/mysql.conf.d/mysql02.cnf

bind-address = mysql02
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = example
#binlog_ignore_db = include_database_name

$ sudo service mysql restart

mysql> create user 'replicator'@'%' identified by 'password'; 
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'replicator'@'%'; 
Query OK, 0 rows affected (0.00 sec)

mysql> create database example; 
Query OK, 1 row affected (0.01 sec)

mysql> change master to master_host = "192.168.0.104", master_user = "replicator", master_password = "password", master_log_file = "mysql-bin.000001", master_log_pos = 607;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_host='mysql01', master_user='replicator', master_password='password', master_log_file='mysql-bin.000003',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 | example      |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_host='mysql01', master_user='replicator', master_password='password', master_log_file='mysql-bin.000003',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

Doing test write from mysql01

mysql > use example;

mysql> create table example.testuser(id int, name varchar(20));

mysql > INSERT INTO `testuser` (`id`, `name`) VALUES (1, 'indra sadik');

mysql > INSERT INTO `testuser` (`id`, `name`) VALUES (2, 'indradhi');

mysql > select * from `testuser`;

mysql> select * from example.testuser;
+------+-------------+
| id   | name        |
+------+-------------+
|    1 | indra sadik |
|    2 | indradhi    |
+------+-------------+
2 rows in set (0.00 sec)

mysql>

Doing test write from mysql02

mysql > use example;

mysql> select * from example.testuser;
+------+-------------+
| id   | name        |
+------+-------------+
|    1 | indra sadik |
|    2 | indradhi    |
+------+-------------+
2 rows in set (0.00 sec)

mysql > INSERT INTO `testuser` (`id`, `name`) VALUES (3, 'denly');

mysql> select * from example.testuser;
+------+-------------+
| id   | name        |
+------+-------------+
|    1 | indra sadik |
|    2 | indradhi    |
|    3 | denly       |
+------+-------------+
3 rows in set (0.00 sec)

Doing test read from mysql01

mysql> select * from example.testuser;
+------+-------------+
| id   | name        |
+------+-------------+
|    1 | indra sadik |
|    2 | indradhi    |
|    3 | denly       |
+------+-------------+
3 rows in set (0.00 sec)

postgresql – pointing postgresql data to new location

Database grow over time, it out growing the space on their partition located.
in redhat 6.7, the default partition for “/” its 50GB and the rest going to “/home” partition.
when you default install postgresql-9.5 it would located in “/opt” which include in “/” partition.
for database production 50GB it’s so small. so i want change the data folder to “/home” which have TB space.

#status postgres
$ /etc/init.d/postgresql-9.5 status
pg_ctl: server is running (PID: 6309)
/opt/PostgreSQL/9.5/bin/postgres "-D" "/opt/PostgreSQL/9.5/data"

# stop postgres
$ service postgresql-9.5 stop
$ /etc/init.d/postgresql-9.5 status
pg_ctl: no server running

# change variable
$ nano /opt/PostgreSQL/9.5/pg_env.sh

export PGDATA=/opt/PostgreSQL/9.5/data
change to
export PGDATA=/home/pg_data

# create dir
$ mkdir -p /home/pg_data

# doing rsync
$ rsync -azP /opt/PostgreSQL/9.5/data/ /home/pg_data/

# just make sure
$ mv /opt/PostgreSQL/9.5/data /opt/PostgreSQL/9.5/data_bak

# backup service script
$ mkdir -p ~/backup/ && mv /etc/rc.d/init.d/postgresql-9.5 ~/backup/

# download postgresql-9.5 file
$ wget https://raw.githubusercontent.com/g3n1k/exsys/master/postgresql-9.5

# move postgresql-9.5 to /etc/rc.d/init.d/
$ mv postgresql-9.5 /etc/rc.d/init.d/

# start postgresql
$ service postgresql-9.5 start

# status postgresql
$ service postgresql-9.5 status
pg_ctl: server is running (PID: 13282)
/opt/PostgreSQL/9.5/bin/postgres "-D" "/home/pg_data"

phpmyadmin – multiple host

using phpmyadmin to manage multiple host

the default, phpmyadmin only host to localhost, there’s no other choice host database

phpmyadmin-default-11072016-061158-am

the problem when you have multiple host database mysql and still love phpmyadmin a client database

solution you can create multiple host database in php myadmin

# edit phpmyadmin config file
geany /var/www/phpmyadmin/config.inc.php

the default config

phpmyadmin-config-before-11072016-060408-am

change to

phpmyadmin-config-after-11072016-060734-am

/*
 * Servers configuration
 */

$i = 0;

$_default_cfg_server = array(
    'auth_type' => 'cookie',
    'verbose'    => 'localhost',
    'host'        => 'localhost',
    'connect_type'=> 'tcp',
    'compress'    => false,
    'AllowNoPassword' => true
);

$servers = array(
    array(), // this our localhost, so let it be
    array(
        'verbose' => 'Stela DB',
        'host'    => '123.456.789.012'
    )
);

foreach($servers as $svr) 
    $cfg['Servers'][++$i] = array_merge($_default_cfg_server, $svr);

save and refresh phpmyadmin login page

phpmyadmin-multiplehost-11072016-060049-am

now your phpmyadmin have multiple host

 

 

cannot create mssql.so on ubuntu 16.04 and php5.6

using php5.6 with freetds installed, and use ubuntu server 16.04

sudo add-apt-repository ppa:ondrej/php
sudo apt-get update
sudo apt-get install apache2 mysql-server php5.6 php5.6-mbstring php5.6-mcrypt php5.6-mysql php5.6-xml php5.6-cli libapache2-mod-php5.6 php5.6-gd freetds-common freetds-bin unixodbc php5.6-sybase php5.6-odbc cifs-utils php5.6-curl

how i know which mssql not working ???

  • try with phpinfo
    sudo nano /var/www/html/info.php
    # paste this code
    <?php phpinfo(); ?>

    open with browser, but i can find mssql there

  • try with php code
    sudo nano /var/www/html/ms.php
    # paste this code
    <?php
    $connection = mssql_connect('mssql-host', 'mssql-user', 'mssql-pass');
    if (!$connection) { die('Unable to connect!'); }
    if (!mssql_select_db('mssql-db', $connection)) { die('Unable to select database!');}
    $result = mssql_query('SELECT * FROM CAPACITY_INFO');
    while ($row = mssql_fetch_array($result)) { var_dump($row); }
    mssql_free_result($result);
    ?>
    # then try
    php /var/www/html/ms.php

    php-mssql

  • success try with tsql

tsql

  • cannot find mssql.so in /usr/lib/php/20131226/
    ls /usr/lib/php/20131226/ | grep mssql

 how to solve

# download the mssql.so
# this mssql get from ubuntu server 64 bit and php 5.6
https://drive.google.com/open?id=0BxV3_TI0LIeYT2pzVzBXRG1sVWM

#upload to your server
scp mssql.so user@server:/home/youruserhome

# move mssql.so to /usr/lib/php/20131226/
sudo mv /home/youruserhome/mssql.so /usr/lib/php/20131226/

# create mssql.ini
sudo nano sudo /etc/php/5.6/mods-available/mssql.ini

# insert this code
extension=mssql.so
# save and close

# copy config mssql.ini 
cd /etc/php/5.6/apache2/conf.d
sudo ln -s /etc/php/5.6/mods-available/mssql.ini 20-mssql.ini
cd /etc/php/5.6/cli/conf.d
sudo ln -s /etc/php/5.6/mods-available/mssql.ini 20-mssql.ini

# restart service apache2
sudo service apache2 restart

test again with phpinfo and script

mssql-phpinfo

success

Ubuntu MySQL table name case insensitive

Open terminal and edit /etc/mysql/my.cnf

sudo nano /etc/mysql/my.cnf

Underneath the [mysqld] section.add:

lower_case_table_names = 1

Restart mysql

sudo /etc/init.d/mysql restart

Then check it here:

mysqladmin -u root -p variables

http://dba.stackexchange.com/questions/59407/how-to-make-mysql-table-name-case-insensitive-in-ubuntu