how connect remote mysql server

how connect remote mysql server :-

By Default remote access to MySQL database server is disabled for security reasons. However, some time you need to provide remote access to database server from home or a web server. so how do i enable remote access to mysql database server ..

there are following steps to allow remote connection to mysql server ..

Step # 1 Login Using SSH (if server is outsite your data center)
First, login over ssh to remote MySQL database server:

ssh user@mysql.test.in

Step # 2: Edit my.cnf File

Once connected you need to edit the MySQL server configuration file my.cnf using a text editor such as vi.

Edit /etc/my.cnf, run:

OR

# vi /etc/my.cnf

Step # 3: Once file opened, locate line that read as follows

[mysqld]

Make sure line skip-networking is commented (or remove line) and add following line

bind-address=YOUR-SERVER-IP

For example, if your MySQL server IP is 20.1.1.2 then entire block should be look like as follows:

[mysqld]

user = mysql

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

port = 3306

basedir = /usr

datadir = /var/lib/mysql

tmpdir = /tmp

language = /usr/share/mysql/English

bind-address = 20.1.1.2

# skip-networking

....

..

....

Where,

§ bind-address : IP address to bind to.

§ skip-networking : Don’t listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should be removed from my.cnf or put it in comment state.

Step# 4 Save and Close the file

Restart the mysql server, enter:
# /etc/init.d/mysql restart

Step # 5 Grant access to remote IP address

Connect to mysql server:
$ mysql -u root -p mysql

Grant access to a new database

If you want to add a new database called foo for user bar and remote IP 20.1.1.1 then you need to type the following commands at mysql> prompt:

mysql> CREATE DATABASE foo;

mysql> CREATE USER 'bar'@'20.1.1.1' IDENTIFIED BY 'PASSWORD';

mysql> GRANT ALL ON foo.* TO bar@'20.1.1.1' IDENTIFIED BY 'PASSWORD';

OR

if for all database than use *.* instead of foo.* ..

mysql> GRANT ALL ON *.* TO bar@'20.1.1.1' IDENTIFIED BY 'PASSWORD';

mysql> flush privileges;

How Do I Grant Access To An Existing Database?

Let us assume that you are always making connection from remote IP called 20.1.1.1 for database called webdb for user webadmin, To grant access to this IP address type the following command At mysql> prompt for existing database, enter:
mysql> update db set Host='20.1.1.1' where Db='webdb';
mysql> update user set Host='20.1.1.1' where user='webadmin';

Step # 5: Logout of MySQL

Type exit command to logout mysql:mysql> exit

Step # 6: Open port 3306

You need to open TCP port 3306 using iptables or BSD pf firewall.

A sample iptables rule to open Linux iptables firewall

/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT

OR only allow remote connection from your web server located at 20.1.1.3:

/sbin/iptables -A INPUT -i eth0 -s 20.1.1.3 -p tcp --destination-port 3306 -j ACCEPT

OR only allow remote connection from your lan subnet 192.168.1.0/24:

/sbin/iptables -A INPUT -i eth0 -s 192.168.1.0/24 -p tcp --destination-port 3306 -j ACCEPT

Finally save all rules:
# service iptables save

A sample FreeBSD / OpenBSD pf rule ( /etc/pf.conf)

pass in on $ext_if proto tcp from any to any port 3306

OR allow only access from your web server located at 20.1.1.3:

pass in on $ext_if proto tcp from 20.1.1.3 to any port 3306 flags S/SA synproxy state

Step # 7: Test it

From your remote system or your desktop type the following command:
$ mysql -u webadmin –h 20.1.1.2 –p
Where,

§ -u webadmin: webadmin is MySQL username

§ -h IP or hostname: 20.1.1.2 is MySQL server IP address or hostname (FQDN)

§ -p : Prompt for password

You can also use telnet to connect to port 3306 for testing purpose:
$ telnet 20.1.1.2 3306

Simply we can execute following command on 20.1.1.2 after allow 3306 port from ip tables .. if root user ..

mysql> CREATE USER 'root'@'20.1.1.1' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.13 sec)

mysql> GRANT ALL ON *.* TO root@'20.1.1.1' IDENTIFIED BY 'pasword';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.09 sec)

Thanks

Si

Read more...

httpd: apr_sockaddr_info_get() failed for HOSTNAME

OR

Starting httpd: Warning: DocumentRoot [/var/www/html/trunk] does not exist
httpd: Could not reliably determine the server's fully qualified domain name, using 17.01.2.1 for ServerName

If you get this error when starting Apache 2 here is the fix…

Performing sanity check on apache22 configuration:
httpd: apr_sockaddr_info_get() failed for someserver1.host-name.net
httpd: Could not reliably determine the server’s fully qualified domain name, using 127.0.0.1 for ServerName
Syntax OK
Starting apache22.
httpd: apr_sockaddr_info_get() failed for someserver1.host-name.net
httpd: Could not reliably determine the server’s fully qualified domain name, using 127.0.0.1 for ServerName

The repair is very easy, just fix your hosts file locate at `/etc/hosts`.

First, determing your hostname of the machine that you are on…

#> hostname
someserver1.host-name.net

Then open your hosts file…

#> vi /etc/hosts

Then change all the host items to match

::1 localhost.someserver1.host-name.net localhost
127.0.0.1 localhost.someserver1.host-name.net localhost
192.1.0.123 someserver1.host-name.net someserver1
192.1.0.123 someserver1.host-name.net.

Then start the server again…

#> apachectl start
Performing sanity check on apache22 configuration:
Syntax OK
Starting apache22.
#> …

All set!!!

Read more...

how to set up master slave mysql replication in mysql linux, centos

how to set up master slave mysql replication in mysql (linux, centos, ubunto, fedora, redhat) ..

how to set up MySQL master-master replication. We need to replicate MySQL servers to achieve high-availability (HA). there are following reason to set up master-slave replication using MySQL.

1) Offload some of the queries from one server to another and spread the load: One of the biggest advantages to have master-slave set up in MySQL is to be able to use master for all of the inserts and send some, if not all, select queries to slave. This will most probably speed up your application without having to diving into optimizing all the queries or buying more hardware.

2) Do backups from slave: One of the advantages people overlook is that you can use MySQL slave to do backups from. That way site is not affected at all when doing backups. This becomes a big deal when your database has grown to multiple gigs and every time you do backups using mysqldump, site lags when table locks happen. For some sites, this could mean that site goes down for few secs to minutes. If you have slave, you just take slave out of rotation (should be built into code) and run backups off the slave. You can even stop slave MySQL instance and copy the var folder instead of doing mysqldump.

there are following steps to configure master slave replication in mysql ...

--------------------------

Step :1
1. Install mysql on master and slave.. configure network services on both system, like Here are some assumptions:
- Master server ip: 11.0.0.20
- Slave server ip: 11.0.0.21
- Slave username: slaveusername
- Slave pw: slavepassword
- Your data directory is: /usr/local/mysql/var/

Step :2
2. Put the following changes in your MASTER my.cnf file :-
# changes made to do master
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1

log-bin=mysql-bin
log-bin
binlog-do-db=database_name_1 # input the database which should be replicated
binlog-do-db=database_name_2
binlog-do-db=database_name_3
binlog-ignore-db=mysql # input the database that should be ignored for replication
binlog-ignore-db=test

server-id=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Step :3
3. Create replication slave user account on master :-
mysql> grant replication slave on *.* to slaveusername@'11.0.0.21' identified by 'slavepassword';

if master not allow to connect slave .. use following to connect slave to master ..

mysql> CREATE USER 'slaveusername'@'11.0.0.21' IDENTIFIED BY 'slavepassword';
mysql> GRANT ALL ON *.* TO 'slaveusername'@'11.0.0.21' IDENTIFIED BY 'slavepassword';
mysql> flush privileges;

Step :4
4. Put the following changes in your SLAVE my.cnf file :-
# changes made to do slave
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1

replicate-do-db=database_name_1
replicate-do-db=database_name_2
replicate-do-db=database_name_3

server-id=2

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Step :5
5. On Mater Do a dump of mysql data to move to slave ..
i . mysql> FLUSH TABLES WITH READ LOCK;
Note :- wait 5-10 sec

ii . mysqldump -u masteruser -p masterpassword --single-transaction --flush-logs --master-data --databases database_name_1 database_name_2 database_name_3 > databasename.`date +%y-%m-%d-%H-%M`.sql

NOTE :- mysql> show master status;
+------------------+----------+------------------------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+------------------------------------+------------------+
| mysql-bin.000163 | 98 | database_name_1 database_name_2 database_name_3 | mysql,test |
+------------------+----------+------------------------------------+------------------+

iii . mysql> UNLOCK TABLES;

Step :6
6. send master databasedump to slave server using ftp OR sftp ..

Step :7
7. import master databasedump on slave
mysql -u slaveusername -p slavepassword < masterdatabasedumpname.sql

Step :8
8. After dump is imported go in to mysql client by typing mysql. Let us tell the slave which master to connect to and what login/password to use:
mysql> CHANGE MASTER TO MASTER_HOST='11.0.0.20', MASTER_USER='masteruser', MASTER_PASSWORD='masterpassword';
-------------------------------
if probem than use
1. check master status at the time of masterbackup ..
mysql> show master status;
+------------------+----------+------------------------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+------------------------------------+------------------+
| mysql-bin.000163 | 98 | database_name_1 database_name_2 database_name_3 | mysql,test |
+------------------+----------+------------------------------------+------------------+
2. same position and bin file use in below command ...
CHANGE MASTER TO MASTER_HOST='11.0.0.20', MASTER_USER='masteruser', MASTER_PASSWORD='masterpassword', MASTER_LOG_FILE='mysql-bin.000163', MASTER_LOG_POS=98;
-------------------------------

Step :9
9. Let us start the slave:
mysql> start slave;

Step :10
10. You can check the status of the slave by typing
mysql> show slave status\G

if set up is proper than it show
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 11.0.0.20
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: MASTERMYSQL01-bin.000009
Read_Master_Log_Pos: 4
Relay_Log_File: MASTERMYSQL02-relay-bin.000015
Relay_Log_Pos: 3630
Relay_Master_Log_File: MASTERMYSQL01-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 4
Relay_Log_Space: 3630
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 1519187
Click here to find out more!

1 row in set (0.00 sec)



Note: --

Master my.cnf file

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

#innodb_force_recovery = 4

#added to fix timeout issues - next 2 lines
innodb_lock_wait_timeout=200
innodb_buffer_pool_size=1024M

#packet size so that the dump restore works
max_allowed_packet=1500M
max_binlog_size=512M
max_connections=2000
#max_connections=250

#fix for creating index on large table
innodb_lock_wait_timeout=2000

#performance related - mysql wouldnt flush at every commit
innodb_flush_log_at_trx_commit=0

#we have enough memory, and very repeated selects , hence use the query cache
query_cache_type=1
query_cache_size=200M

#required for mysql master slave
log-bin=mysql-bin
log-bin
binlog-do-db=database_name_1 # input the database which should be replicated
binlog-do-db=database_name_2
binlog-do-db=database_name_3
binlog-ignore-db=mysql # input the database that should be ignored for replication
binlog-ignore-db=test
server-id=1

# Be aware that this log type is a performance killer.
#log = /var/log/mysql/mysql.log

long_query_time=1
log-slow-queries=/var/log/mysql/mysql-slow.log

#cache some threads
thread_cache = 40

[mysql.server]
user=mysql
basedir=/var/lib

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


Slave my.cnf file :-

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

#added to fix timeout issues - next 2 lines
innodb_lock_wait_timeout=200
innodb_buffer_pool_size=500M
##innodb_buffer_pool_size=1024M

#packet size so that the dump restore works
max_allowed_packet=1024M
##max_connections=2000
max_connections=300

#fix for creating index on large table
##innodb_lock_wait_timeout=2000

#performance related - mysql wouldnt flush at every commit
innodb_flush_log_at_trx_commit=0

#we have enough memory, and very repeated selects , hence use the query cache
query_cache_type=1
query_cache_size=200M
query_cache_limit = 2M
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=4M

#required for mysql master slave
#log-bin=mysql-bin
replicate-do-db=database_name_1
replicate-do-db=database_name_2
replicate-do-db=database_name_3
server-id=2

#master-host = 11.0.0.20
#master-user = masteruser
#master-password = masterpassword
#master-port = 3306

#relay-log=mysqld-relay-bin

# Be aware that this log type is a performance killer.
#log = /var/log/mysql/mysql.log

long_query_time=1
log-slow-queries=/var/log/mysql/mysql-slow.log

#cache some threads
thread_cache = 40

[mysql.server]
user=mysql
basedir=/var/lib

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Read more...