Mysqld-bin logs problem

After continuous running of Mysql server, I’ve noticed that /var/lib/mysql directory uses too much disk space. The reason of that problem was a set of mysqld-bin.xxxxxx files. Each of that file was 1GB in size. First I thought that I can stop the Mysql server and remove that files, but I didn’t want to act this way because there was sensitive data in databases that I didn’t want to loose. So I found the better way to achieve this. Connect to Mysql server and perform the following

mysql> flush logs;

mysql> reset master;

That’s it! After that the all logbin files should be removed. Also you can disable mysqld-bin logging completely by commenting out log-bin line in my.cnf and restarting Mysql server daemon.

Backup and Restore MySQL Database Using mysqldump

mysqldump is an effective tool to backup MySQL database. It creates a *.sql file with DROP table, CREATE table and INSERT into sql-statements of the source database. To restore the database,  execute the *.sql file on destination database.  For MyISAM, use mysqlhotcopy method that we explained earlier, as it is faster for MyISAM tables.

Using mysqldump, you can backup a local database and restore it on a remote database at the same time, using a single command. In this article, let us review several practical examples on how to use mysqldump to backup and restore. Continue reading

MySQL: INSERT IF NOT EXISTS syntax

To start: as of the latest MySQL, syntax presented in the title is not possible. But there are several very easy ways to accomplish what is expected using existing functionality.

There are 3 possible solutions: using INSERT IGNORE, REPLACE, or INSERT … ON DUPLICATE KEY UPDATE. Continue reading

10 things you should know about NoSQL databases

Takeaway: The relational database model has prevailed for decades, but a new type of database — known as NoSQL — is gaining attention in the enterprise. Here’s an overview of its pros and cons.

 

For a quarter of a century, the relational database (RDBMS) has been the dominant model for database management. But, today, non-relational, “cloud,” or “NoSQL” databases are gaining mindshare as an alternative model for database management. In this article, we’ll look at the 10 key aspects of these non-relational NoSQL databases: the top five advantages and the top five challenges.

Note: This article is also available as a PDF download. Continue reading

MySQL LEFT, RIGHT JOIN tutorial

MySQL joins are hard for beginners. At least for me when I was beginner.
I will try to explain the joins in the simplest possible way.

Join in MySQL is a query where you can join one or more tables.

For example we have two tables: products and buyers with the following structures. Continue reading

MySQL show users – how to show the users in a MySQL database

MySQL users FAQ: How do I show MySQL users (How do I show user accounts I’ve created in a MySQL database)?

To show/list the users in a MySQL database, first log into your MySQL server as an administrative user, then run this MySQL query:

select * from mysql.user;

This query shows a large listing of MySQL user information, including user permission information, so you may want to trim down some of the fields to display. You can get a listing of the fields in the mysql.user table by running this MySQL query: Continue reading

How to examine and kill MySQL client processes

A useful MySQL command, when it comes to keeping track of client-server transactions, is SHOW PROCESSLIST. It lists all the open connections to the server, together with a thread ID, information on the requesting host, user and database, the query string, and the current state of the connection. The command itself is simple to run from the MySQL client: Continue reading

Showing running queries in MySQL

MySQL has a statement called “show processlist” to show you the running queries on your MySQL server. This can be useful to find out what’s going on if there are some big, long queries consuming a lot of CPU cycles, or if you’re getting errors like “too many connections”. Continue reading