Server Administration & Management

When you get the SQL Running “NO” error, do not use slave skip counter as shown below.

mysql> stop slave;SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;start slave;

The above statement will skip one sql statement and execute the next statement found in the binary log file. When you get the following “Could not parse relay log event entry” error, it means that Relay log file is corrupt. The IO thread is running and SQL thread is stopped. SQL thread may be broken due to 2 reasons.

1) SQL statement can not be executed. 2) OR slave is stopped due to Relay log event entry.

1.1) In case of first case use skip counter statement.

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

1.2) In case of second case when SQL relay log is corrupt you need to restart the slave by using “change master to” statement as shown below.

The master IP address along with username, password can be found in master.info file. Relay log info file has the binary log file name and position. These 2 files are found in MySQL data directory. In this case these files can be found in /var/lib/mysql/ folder.

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:

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.

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:

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]

# vim /etc/my.cnf
———————————————————————————————-log-bin # is the binary log basename to generate binary log file names.binlog-do-db=<database name> # input the database which should be replicatedbinlog-ignore-db=mysql # input the database that should be ignored for replicationserver-id = 199 # option is used in replication to enable master and slave servers to identify themselves uniquely. On the master and #each slave, you must use the server-id option to establish a unique replication ID in the range from 1 to 231
———————————————————————————————-

# vim /etc/my.cnf
———————————————————————————————-log-bin # is the binary log basename to generate binary log file names.binlog-do-db=<database name> # input the database which should be replicatedbinlog-ignore-db=mysql # input the database that should be ignored for replicationlog-slave-updates # makes the slave log the replicated events to its binary logserver-id = 201 # option is used in replication to enable master and slave servers to identify themselves uniquely. On the master and #each slave, you must use the server-id option to establish a unique replication ID in the range from 1 to 231
———————————————————————————————-

Note:-Eventually, I find the reason. The password is too long. So please try to use a short password and it will be work. I think mysql command may truncate the password if it is too long, but it appears the replication didn’t do that for you.

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

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.

# 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.

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.

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.