UnixServerAdmin

Server Administration & Management

Protected: How to reconfigure MySQL Slave server after Break

This content is password protected. To view it please enter your password below:

June 21, 2013 Posted by | MySQL | , | Enter your password to view comments.

How to monitor MySQL restore progress using PV

pv command allows a user to see the progress of data through a pipeline, by giving information such as time elapsed, percentage completed (with progress bar), current throughput rate, total data transferred, and ETA. To use it, insert it in a pipeline between two processes, with the appropriate options. Its standard input will be passed through to its standard output and progress will be shown on standard error.

A great application of pv is when you’re restoring large amounts of data into MySQL, especially if you’re restoring data under duress due to an accidentally-dropped table or database. The standard way of restoring data is something we’re all familiar with:

# mysql -uroot -p < database_backup.sql

The downside of this method is that you have no idea how quickly your restore is working or when it might be done. You could always open another terminal to monitor the tables and databases as they’re created, but that can be hard to follow. Toss in pv and that problem is solved:

# pv database_backup.sql | mysql -p
96.8MB 0:00:17 [5.51MB/s] [=======================>          ] 71% ETA 0:02:10

When it comes to MySQL, your restore rate is going to be different based on some different factors, so the ETA might not be entirely accurate.

February 1, 2013 Posted by | MySQL | , | Leave a comment

How to bind to multiple IP Address in MySQL Server

The MySQL server listens on a single network socket for TCP/IP connections. This socket is bound to a single address, but it is possible for an address to map onto multiple network interfaces. The default address is 0.0.0.0. To specify an address explicitly, use the bind-address=addr option at server startup, where addr is an IPv4 address or a host name. If addr is a host name, the server resolves the name to an IPv4 address and binds to that address. The server treats different types of addresses as follows:

* If the address is 0.0.0.0, the server accepts TCP/IP connections on all server host IPv4 interfaces.
* If the address is a “regular” IPv4 address (such as 127.0.0.1), the server accepts TCP/IP connections only for that particular IPv4 address.

Configuration

You can set bind-address directive in my.cnf. Edit /etc/my.cnf or /usr/local/etc/my.cnf, run:

# vim /etc/my.cnf

bind-address  = 0.0.0.0

Make sure you delete the following line or comment out the following line:

#skip-networking

Save and close the file. Next setup the firewall and allows connection from or to select IPs only.

December 3, 2012 Posted by | MySQL | | Leave a comment

MySQL Optimization Tips

Sample values for each variables

max_connections=400
max_user_connections=30
key_buffer=256M (128MB for every 1GB of RAM)
myisam_sort_buffer_size=64M
join_buffer_size=1M
read_buffer_size=1M (1MB for every 1GB of RAM)
sort_buffer_size=1M (1MB for every 1GB of RAM)
table_cache=1500
thread_concurrency=2 (Number of CPUs x 2)
thread_cache_size=128
wait_timeout=10
connect_timeout=5
max_allowed_packet=16M
max_connect_errors=10
query_cache_limit=1M
query_cache_size=32M (32MB for every 1GB of RAM)
query_cache_type=1

June 25, 2012 Posted by | MySQL | | Leave a comment

How to Backup and Restore large MySQL Database with Compression Method

If you have very large mysql database then it is very hard to backup and restore using the conventional phpmyadmin or any other programs.

To Backup MySQL Database

# mysqldump -u [username] -p [password] [dbname] > [backup.sql]

If your mysql database is very big, you might want to compress the output of mysql dump.

Just use the mysql backup command below and pipe the output to gzip, then you will get the output as gzip file.

# mysqldump -u [username] -p [password] [dbname] | gzip -9 > [backup.sql.gz]

To Restore MySQL Database, you need to create the database in target machine then use this command

# mysql -u [username] -p [password] [dbname] < [backup.sql]

Restore Compressed MySQL Database

# gunzip < [backup.sql.gz] | mysql -u [username] -p [password] [dbname]

March 3, 2012 Posted by | MySQL | , | Leave a comment

How to Remove MySQL Binary Log

MySQL configuration, by default, maintains binary logs. These logs “contain all statements that update data or potentially could have updated it (for example, a DELETE which matched no rows). Statements are stored in the form of ‘events’ that describe the modifications. The binary log also contains information about how long each statement took that updated data.” This is fine and all, but (again by default) these log files are never deleted. There is a (configurable) max file size for each log, but MySQL simply rolls over to a new log when it’s reached. Additionally, MySQL rolls over to a new log file on every (re)start. After a few months of operation, it’s easy to see how this can take up a lot of space.

MySQL Binary Log stores query event such as add, delete and update in a very details way. The Binary Log is used for two main purposes;

Data Recovery : It may be used for data recovery operations. After a backup file has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.

High availability / replication : The binary log is used on master replication servers as a record of the statements to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master.

Yes, as long as the data is replicated to Slave server, it’s safe to remove the file. It’s recommend only remove MySQL Binary Log older than 1 month. Besides, if Recovery of data is the main concern, it’s recommend to archive MySQL Binary Log. There are several ways to remove or clean up MySQL Binary Log, it’s not recommend to clean up the file manually means running the remove command.

Finally, for the current set, login to MySQL as an admin user (eg., mysql -u root -p). You’ll want to run the following two commands:

Reset Master statement is uses for new database start up during replication for Master and Slave server. This statement can be used to remove all Binary Log.

To clean up Binary Log on Master Server

# mysql -u username -p

mysql> flush logs;

mysql> reset master;

To clean up Binary Log on Slave Server

# mysql -u username -p

mysql> flush logs;

mysql> reset slave;

Above command will empty the binary logs, but not remove them. That’s it. Depending on the size and number of your logs, those two commands may take a while to run, but the end result is that any unsaved transactions will be flushed to the database, all older logs will be dropped, and the log index will be reset to 1.

Here is how to purge or clean the MySQL binary logs (NEVER remove them from the filesystem manually). PURGE BINARY LOGS statement can remove Binary Log base on date or up to a Binary Log sequence number

mysql> purge binary logs to ‘mysql-bin-log.000015’;

Alternatively, you can remove the binary older than a specific date.

mysql> purge binary logs before ‘2012-02-02 22:46:26’;

The BEFORE variant’s datetime_expr argument should evaluate to a DATETIME value (a value in ‘YYYY-MM-DD hh:mm:ss’ format). Above commands will remove the binary logs from the disk.

mysql> show binary logs;

To obtain a listing of the binary logs on the master server with above command.

The above commands should not be used when/before binary logs are used for data integrity check or for replication. You can also add to /etc/my.cnf the following Code:

expire_logs_days = 5

in order to keep only the last 5 days binary logs.

February 10, 2012 Posted by | MySQL, Tips & Tricks | , , , | Leave a comment

Protected: How to reset MySQL Slave Replication

This content is password protected. To view it please enter your password below:

January 19, 2012 Posted by | MySQL | , | Enter your password to view comments.

Backup_dump.sh

##########################################################
# Database Server Backup #######################################
# backup_dump.sh script #######################################
##########################################################
set -x
set -v
standby=$1
myfirstfile=”ORS`date +’%d-%b-%Y-%H-%M’`.sql”
myfile=”ORS`date +’%d-%b-%Y-%H-%M’`.sql.bz2″

# mysql dump, check, scp and report
mysqldump -uroot -pPASSWORD –all-databases –routines –flush-logs –single-transaction –master-data=2 > /backup/mysqldump/$myfirstfile 2> /backup/mysqldump/ORS_err.txt

cd /backup/mysqldump/
bzip2  $myfirstfile

scp /backup/mysqldump/$myfile backup@$standby:/backup/autobackup/ORSmysql/
scp /backup/mysqldump/$myfile backup@X.X.X.X:/backup/autobackup/ORSmysql/
##########################################################

January 11, 2012 Posted by | MySQL, Shell Script | , | Leave a comment

How to Reset MySQL Root Password

Method 1. How to Change MySQL Root Password Using mysqladmin Command?

You can change the MySQL root password using mysqladmin command as shown below. Please note that there is no space between -p and currentpassword.

# mysqladmin -u root -pCURRENTPASSWORD password ‘NEWPASSWORD’

Once you’ve changed it make sure you can login with your new password successfully as shown below.

# mysql -u root -pnewpassword

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 8
Server version: 5.5.13-rc-community MySQL Community Server (GPL)
mysql>

************************************************************************************************

Method 2. How to Change MySQL Root Password From MySQL Prompt Using UPDATE SQL Command?

You can also use the standard update SQL command combined with the MySQL password function to change the password as shown below. Login to MySQL root account using old password

# mysql -u root -poldpassword

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 8
Server version: 5.5.13-rc-community MySQL Community Server (GPL)
mysql>

Use the UPDATE Command to change root password.

mysql> UPDATE user SET password=PASSWORD(‘newpassword’) WHERE user=’root’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verify the new MySQL root password

Once you’ve changed it make sure you can login with your new password successfully as shown below.

# mysql -u root -pnewpassword

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 8
Server version: 5.5.13-rc-community MySQL Community Server (GPL)
mysql>

************************************************************************************************

Method 3. How to Set MySQL Root Password Using mysqladmin Command?

This method works only if there is no password currently assigned for the root account. If you don’t have any password assigned to the root account, set the password without giving current password as shown below.
# mysqladmin -u root password ‘newpassword’ [Note: There is no currentpassword for root in this example]

How to Change MySQL Regular User (non-root) Password Using mysqladmin Command?

# mysqladmin -u jsmith -pcurrentpassword password ‘newpassword’

How to Change MySQL Regular User (non-root) Password From MySQL Prompt Using UPDATE SQL Command?

mysql> UPDATE user SET password=PASSWORD(‘newpassword’) WHERE user=’ramesh’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

************************************************************************************************

Method 4. Recover MySQL Root Password

Step-1:- Stop mysql service

# /etc/init.d/mysql stop

Output:
Stopping MySQL database server: mysqld.

Step-2:- Start to MySQL server w/o password:

# mysqld_safe –skip-grant-tables &

Output:
[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

Step-3:- Connect to mysql server using mysql client:

# mysql -u root

Output:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1 to server version: 5.5.13-rc-community MySQL Community Server (GPL)
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.
mysql>

Step-4:- Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD(“NEW-ROOT-PASSWORD”) where User=’root’;
mysql> flush privileges;
mysql> quit

Step-5:- Stop MySQL Server:

# /etc/init.d/mysql stop

Output:
Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended
[1]+  Done                    mysqld_safe –skip-grant-tables

Step-6:- Start MySQL server and test it

# /etc/init.d/mysql start

# mysql -u root -p

January 7, 2012 Posted by | MySQL | , | Leave a comment

Protected: mysql_replication_slave.sh

This content is password protected. To view it please enter your password below:

January 3, 2012 Posted by | MySQL, Shell Script | , | Enter your password to view comments.

Protected: How to configure MySQL Master-Slave Replica

This content is password protected. To view it please enter your password below:

November 11, 2011 Posted by | MySQL | , | Enter your password to view comments.

MySQL error : Can’t create new tempfile: ‘*.TMD file

If you are getting Can’t create new tempfile: ‘tablesname.TMD file error while repairing corrupted database tables please try use following command to fix it

# myisamchk -r -f  tables.MYI

September 27, 2011 Posted by | MySQL | | Leave a comment

How to change MySQL database directory to another partition as /var patition full

you might have faced the issue of /var partition gettting full regularly due to database directory. If you have a larger partition with free space, then it is possible to move the database directory to the larger partition.  Here are the steps :-

1. Switch off the database server while we are moving the databases.

# /etc/rc.d/init.d/mysql stop

2. As considering that I have enough space in /home partition. Here goes my new database data directory as

# mkdir /home/mysql

3. Now it is better to copy the database first, rather than move.

# cp -prdf /var/lib/mysql  /home/

# mv /var/lib/mysql /var/lib/mysql-bk

4. We are copying the database to the new location since it is better to revert back the settings with minimum downtime, if anything goes wrong. Take a backup of /etc/my.cnf

# cp /etc/my.cnf  /etc/my.cnf.bak

5. Now edit /etc/my.cnf

# vi /etc/my.cnf
———————————————————————
[mysqld]
datadir=/home/mysql
socket=/var/lib/mysql/mysql.sock
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time=1
wait_timeout=300
connect_timeout=60
interactive_timeout=300

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

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

Please note that you don’t specify the socket file location in my.cnf since it causes issues with phpMyadmin

1. For, cPanel server, edit the phpMyadmin configuration take a backup of “/usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php”

# cp /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php  /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php.bak

2. edit this file /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php

# vi /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php

3. add the following lines. If they already exist, edit as below.
(the connect_type usually exist at “tcp” change it to “socket”)

$cfg[‘Servers’][$i][‘socket’] = ‘/home/mysql/mysql.sock’;
$cfg[‘Servers’][$i][‘connect_type’] = ’socket’;

4. Now start the database server.

# /etc/ini.d/mysql start

If it starts fine, you are done. Check the database connections of your site. You can now remove the directory /var/lib/mysql-bk

August 31, 2011 Posted by | cPanel, MySQL | , , | Leave a comment

mysql has failed, please contact the sysadmin (result was “mysql has failed”)

Now a most common problem we are facing with the mysql on newly installed server if we have mysql 5 we mostly receive following error message when starting mysql service from WHM

“mysql has failed, please contact the sysadmin (result was “mysql has failed”).”

As well as when ever we run mysql from shell its gives error message

“Access denied for user ‘root’@’localhost’ (using password: YES)”.

In that case we need to remove mysql from server and reinstall it by using following commands.

# which mysql
/usr/bin/mysql

#mv /usr/bin/mysql /usr/bin/mysql-back

# /scripts/mysqlup  –force

Now reset the root password from WHM –> Main –> SQL Services –> MySQL Root Password

Now you will able to access mysql without any problem.

——————————————————————————————————————

Some time above solution also not allowing to access mysql still throws following error message.

# mysql

“Access denied for user ‘root’@’localhost’

In that case refer following steps but carefully as it will cause you database loss.

# /etc/init.d/mysql stop

# mv /var/lib/mysql/    /var/lib/mysql-back

# mv /etc/my.cnf     /etc/my.cnf-back

# /scripts/mysqlup –force

# /etc/init.d/mysql start

# mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1430564
Server version: 5.0.85-community MySQL Community Edition (GPL)

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement.

mysql> q
Bye

Now you can restore the databases from /var/lib/mysql-back to /var/lib/mysql

# cp -rvf /var/lib/mysql-back    /var/lib/mysql

August 29, 2011 Posted by | MySQL | | Leave a comment

How to Installing MyTop on WHM-cPanel Server

MyTop is one of the best MySQL monitoring tools available, Its vital for monitoring your MySQL health, especially in a shared hosting environment. Installation on a RHEL/CentOS (for cPanel) server is quite straight forward with this guide.
First off, we need a few perl modules, cPanel’s realperlinstaller comes in handy, if you aren’t on cPanel, you’ll need to CPAN or manually install these modules.

# /scripts/realperlinstaller –force Getopt::Long

# /scripts/realperlinstaller –force DBI

# /scripts/realperlinstaller –force DBD::mysql

# /scripts/realperlinstaller –force Term::ReadKey

Now, we download and install mytop.

# wget http://jeremy.zawodny.com/mysql/mytop/mytop-1.6.tar.gz

# tar zxpfv mytop-1.6.tar.gz

# cd mytop-1.6

# perl Makefile.PL && make && make install

NOTE: You may get following error in CentOS 5.x
Error in option spec: “long|!”

Search for the line in Makefile.PL

“long|!”              => $config{long_nums},

Change it to by commenting using #

#”long|!”              => $config{long_nums},

Easy fix.
After installing mytop you need to create a new file under /root/.mytop (mytop config file for root) with the lines below (mysql root password is found on /root/.my.cnf:

user=root
pass=<your mysql password>
host=localhost
db=mysql
delay=5
port=3306
socket=
batchmode=0
header=1
color=1
idle=1

Now you can run mytop

# mytop -d mysql

August 28, 2011 Posted by | cPanel, MySQL | , , | Leave a comment

InnoDB: No valid checkpoint found

You may find this error while restarting the mysql service on the server. Error is as follows:-

# /etc/init.d/mysql restart
Shutting down MySQL…                           [  OK  ]
Starting MySQL.                                         [  OK  ]

# tail -f /var/log/mysqld.log
100825 08:25:02  mysqld started
InnoDB: No valid checkpoint found.
InnoDB: If this error appears when you are creating an InnoDB database,
InnoDB: the problem may be that during an earlier attempt you managed
InnoDB: to create the InnoDB data files, but log file creation failed.

To fix this goahead with the steps given below;-

# cd /var/lib/mysql/

# rm -rvf  ib_logfile0

# rm -rvf  ib_logfile1

# rm -rvf  ibdata1

# /etc/init.d/mysql restart
Shutting down MySQL…..                         [  OK  ]
Starting MySQL……                                 [  OK  ]

Check the InnoDB engine status now. It should be enabled now.

mysql> show engines;
+————+———+—————————————————————-+
| Engine     | Support | Comment                                                        |
+————+———+—————————————————————-+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     |
| BerkeleyDB | NO      | Supports transactions and page-level locking                   |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE    | YES     | Example storage engine                                         |
| ARCHIVE    | YES     | Archive storage engine                                         |
| CSV        | YES     | CSV storage engine                                             |
| ndbcluster | NO      | Clustered, fault-tolerant, memory-based tables                 |
| FEDERATED  | YES     | Federated MySQL storage engine                                 |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          |
| ISAM       | NO      | Obsolete storage engine                                        |
+————+———+—————————————————————-+

August 26, 2011 Posted by | MySQL | | Leave a comment

Horde Session Issue

There is a very common  issue where Horde prevents access to webmail by repeatedly redirecting viewers to Language Selection screen. The following commands, executed as root, will solve these issues:

# /usr/local/cpanel/bin/checkperlmodules

# /scripts/fullhordereset

# myisamchk -r /var/lib/mysql/horde/horde_sessionhandler.MYI

If still you have problem then check the ownership for session directory present in /var/cpanel/userhomes/cpanelhorde directory.

# ll /var/cpanel/userhomes/cpanelhorde

drwx–x–x 4 cpanelhorde cpanelhorde 4096 Jun  5  2011 ./
drwx–x–x 7 root        root        4096 Dec 17 01:48 ../
drwxr-x— 2 cpanelhorde cpanelhorde 4096 Jun  5  2011 mail/
drwx—— 2 cpanelhorde cpanelhorde 4096 Jan 27 14:01 sessions/

Session directory should be cpanelhorde.cpanelhorde ownership recursively.

 

 

August 24, 2011 Posted by | MySQL | , | Leave a comment

Install pdo_mysql

1. Basically, you will need to compile pear and mysql from your Apache. Run the command as below to get it updated.

# yum install php-devel php-pear mysql-devel httpd-devel

# pecl install pdo

# PHP_PDO_SHARED=1 pecl install pdo_mysql

2. Open your php.ini file and insert the line as below.

extension=pdo.so
extension=pdo_mysql.so

3. Restart the Apache services.

# /etc/init.d/https restart

August 23, 2011 Posted by | MySQL, PHP | , , | Leave a comment

MySQL database Directory

MySQL uses files to store data. By default, these data files are locate under the “/var/lib/mysql” directory, where databasename is the name of the database. Also we can store mysql database under desire location with the help of my.cnf, Basically my.cnf file is MySQL configuration file. MySQL Database are three file types:

.FRM –> file contain the table schema.
.ISD –> is the file that actually holds the data.
.ISM –> is the file that provides quick access between the two of them.

August 21, 2011 Posted by | MySQL | | Leave a comment

Features of MySQL

MySQL is a full-featured relational database management system. It is very stable and has proven itself over time. MySQL has been in production for over 10 years.

– MySQL is a multithreaded server. Multithreaded means that every time someone establishes a connection with the server, the server program creates a thread or process to handle that client’s requests. This makes for an extremely fast server. In effect, every client who connects to a MySQL server gets his or her own thread.

– MySQL is also fully ANSI SQL92-compliant. It adheres to all the standards set forth by the American National Standards Institute.

– Another feature of MySQL is its portability, it has been ported to almost every platform. This means that you don’t have to change your main platform to take advantage of MySQL. And if you do want to switch, there is probably a MySQL port for your new platform.

– MySQL also has many different application programming interfaces (APIs). They include APIs for Perl, TCL, Python, C/C++, Java (JDBC), and ODBC.

August 20, 2011 Posted by | MySQL | | Leave a comment