Tag Info

Hot answers tagged

2

Users on MySQL are sharing the same context in terms of databases. So if you are creating a database for one user, it will be also visible to user2. You could solve this issue by prefixing your tables for each user, e.g. abc_item1 and setting correct privileges via grant for it -- so only one user is allowed to use this table. Another way would be to ...


2

The replication filter you have in place Replicate_Wild_Do_Table: zo_dev_matrix.* is actually a little misleading. In the MySQL Documentation, the legal characters for wildcards are %, _ and \_. (If you want to interpret a literal underscore). The asterisk character is not listed. The above filter is actually looking for a table called zo_dev_matrix.*. ...


2

If the master crashes, then it doesn't really matter what you do as far as stopping the slave, as long as you observe the slave once the master is back online, to verify that it has successfully started reading and executing events from the master again. If you don't stop it, the slave should still be fine, and will sit and continually try to reconnect to ...


1

When possible, you should run slave stop on the slave after you have stopped traffic to the master. That said, in most cases the salve will reconnect on its own as long as [a] there isn't an re-attempt limit, [b] the last slave read concluded properly and [c] the binlog on the master side hasn't been corrupted.


1

There are no differences between all the SQL statements, except the third one you're using a different name for the unique index, that's all. All of them creates a unique key/index, constraint md5_constraint unique (query_md5) has the same effect. Just pick the name you prefer for the unique key and feel free to use any of your queries. :)


1

You can take a backup from the live MySQL with Xtrabackup. Percona provides deb repository for Ubuntu. To install the repo for Ubuntu 12.04 follow instructions: Install the key: # apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A Add the source file: # cat /etc/apt/sources.list.d/percona.list deb http://repo.percona.com/apt precise ...


1

If you dropped a 4GB MyISAM table and still have 100% usage of the disk, then something else is clogging up the disk. This is especially true if /var/lib/mysql is on the same mount and /root. Here is what you can do to start investigating: Go to the OS and do this: cd /var/lib/mysql df -h . If the root partition is 100% despite dropping 4GB, your ...


1

According to the MySQL Documentation on SELECT ... INTO The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database ...


1

For logging slow queries do like below, It will log all queries those are not using indexes as well as which are taking more than 1 sec slow_query_log = 1 slow_query_log_file = /var/log/mysqld_slow_query.log long_query_time = 1 log-queries-not-using-indexes For Error log log-error = ...


1

Unfortunately, it is O(N). Internally MySQL creates a new table with the desired structure, copies all your data over to that, and then finally swaps datafiles to effect a drop of the original table with a "rename" of the new. You can find this in MySQL's manual -- it's buried (hence why I can't find the direct link for you right this moment), but it's ...


1

This is so funny. Yesterday, someone asked how to do the opposite and I answered that question : Migrate from innodb_file_per_table to off in MySQL Here the steps for what you want STEP #1 Set innodb_file_per_table to 0 in my.ini [mysqld] innodb_file_per_table = 1 STEP #2 Login in Command Prompt in Administrator mode and run C:\> net stop mysql ...


1

OBSERVATION #1 If you look at the choices made in the explain plan, you could be fooled by something we take for granted with InnoDB tables. The InnoDB Storage Engine tends to dive into index pages to guess at cardinalities. I have stated in my earlier posts: Jun 21, 2011 : From where does the MySQL Query Optimizer read index statistics? Aug 03, 2011 : ...


1

It sounds like the table is on the cusp of a cutover for the query optimizer between the estimated total rows and estimated rows being looked up. The use/ignore index syntax exists for situations like this. The pitfall to this approach is when the table dynamics change in the future such that it's really not the best choice.



Only top voted, non community-wiki answers of a minimum length are eligible