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.
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
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
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
Note: This is precursor post to my talk at Mongo Boston on September 20th. It’s gonna be at Microsoft’s NERD (which I hear is COMPLETELY AWESOME). If you haven’t signed up yet, stop being lame. It’s gonna be awesome. http://www.10gen.com/conferences/mongoboston2010
A lot of people come up to me and ask about MongoDB. Here’s a 101 for those of you still totally in the dark. Continue reading
“JOIN” is a SQL keyword used to query data from two or more related tables. Unfortunately, the concept is regularly explained using abstract terms or differs between database systems. It often confuses me. Developers cope with enough confusion, so this is my attempt to explain JOINs briefly and succinctly to myself and anyone who’s interested. Continue reading
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 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
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
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