Hot answers tagged

301

If you're just importing from a dump file from the CLI on *nix, e.g. mysql -uxxx -pxxx dbname < /sqlfile.sql then first install pipe viewer on your OS then try something like this: pv sqlfile.sql | mysql -uxxx -pxxxx dbname which will show a progress bar as the program runs. It's very useful and you can also use it to get an estimate for mysqldump ...


284

Your innodb_buffer_pool_size is enormous. You have it set at 20971520000. That's 19.5135 GB. If you only have 5GB of InnoDB data and indexes, then you should only have about 8GB. Even this may be too high. Here is what you should do. First run this query SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM (SELECT SUM(data_length+index_length) ...


279

Update in 2019-10-29 As mentions by @Manuel Jordan in comments, utf8mb4_0900_ai_ci is the new default in MySQL 8.0, so the following is now again a better practice: CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; Answer before 2019-10-29 Note: The following is now considered a better practice (see bikeman868's answer): CREATE ...


166

If you like the Parent and Child terms and you feel they are easy to be remembered, you may like the translation of ON DELETE CASCADE to Leave No Orphans! Which means that when a Parent row is deleted (killed), no orphan row should stay alive in the Child table. All children of the parent row are killed (deleted), too. If any of these children has ...


155

Please do not just delete them in the OS. You need to let mysqld do that for you. Here is how mysqld manages it: The file mysql-bin.[index] keeps a list of all binary logs mysqld has generated and auto-rotated. The mechanisms for cleaning out the binlogs in conjunction with mysql-bin.[index] are: PURGE BINARY LOGS TO 'binlogname'; PURGE BINARY LOGS BEFORE '...


145

The valid syntax is close to your second try, but you need to escape the column names with backticks not with single quotes: ALTER TABLE `blog` CHANGE COLUMN `read-more` `read_more` VARCHAR(255) NOT NULL;


117

I am not very familiar with your needs, but perhaps storing each data point in the database is a bit of overkill. It sound almost like taking the approach of storing an image library by storing each pixel as a separate record in a relational database. As a general rule, storing binary data in databases is wrong most of the time. There is usually a better ...


116

From my guide How to support full Unicode in MySQL databases, here are the queries you can run to update the charset and collation of a database, a table, or a column: For each database: ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; For each table: ALTER TABLE table_name CONVERT TO CHARACTER SET ...


111

I once worked with a very large (Terabyte+) MySQL database. The largest table we had was literally over a billion rows. This was using MySQL 5.0, so it's possible that things may have improved. It worked. MySQL processed the data correctly most of the time. It was extremely unwieldy though. (If you want six sigma-level availability with a terabyte of data, ...


104

Percona's Vadim Tkachenko made this fine Pictorial Representation of InnoDB You definitely need to change the following innodb_buffer_pool_size = 4G innodb_log_buffer_size = 256M innodb_log_file_size = 1G innodb_write_io_threads = 16 innodb_flush_log_at_trx_commit = 0 Why these settings ? innodb_buffer_pool_size will cache frequently read data ...


95

select * from information_schema.user_privileges; EDIT: As mentioned by Shlomi Noach: It does not list database-specific, table-specific, column-specific, routine-specific privileges. Therefore, the grant GRANT SELECT ON mydb.* TO myuser@localhost does not show in information_schema.user_privileges. The common_schema solution presented above ...


92

All Data is InnoDB This is what will give you an exact point-in-time snapshot of the data: mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql --single-transaction produces a checkpoint that allows the dump to capture all data prior to the checkpoint while receiving incoming changes. Those incoming changes ...


90

Use this one command: RENAME TABLE foo TO foo_old, foo_new To foo; It is an atomic operation: both tables are locked together (and for a very short time), so any access occurs either before or after the RENAME.


88

A very good thread on this subject is to be found here and also here. The definitive guide for MySQL is, of course, the documentation, to be found here. In the SQL 2003 standard there are 5 different referential actions: CASCADE RESTRICT NO ACTION SET NULL SET DEFAULT To answer the question: CASCADE ON DELETE CASCADE means that if the parent record is ...


82

Rather than killing the process, it would be safer if you did it within MySQL: $ mysqladmin processlist -u root -p Enter password: +-----+------+-----------+-------------------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+------+-----------+-------------------+...


76

UPDATE ips INNER JOIN country ON ips.iso = country.iso SET ips.countryid = country.countryid Using MySQL update multiple table syntax: 14.2.11 UPDATE Syntax Note that you have two different lengths and data types on your iso columns. There are, in fact, two separate sets of ISO codes, 2-letter and 3-letter, so you may not in reality be able to join ...


76

A note to expand on the answer by RolandoMySQLDBA. The script he included is a great approach for including (and table_name in) or excluding (and table_name NOT in) a list of tables. If you just need to exclude one or two tables, you can exclude them individually with the --ignore-table option: mysqldump -u -p etc. --ignore-table=Database.Table1 --ignore-...


74

What would address your question is the subject JOIN DECOMPOSITION. According to Page 209 of the Book You can decompose a join by running multiple single-table queries instead of a multitable join, and then performing the join in the application. For example, instead of this single query: SELECT * FROM tag JOIN tag_post ON tag_post.tag_id = tag.id JOIN post ...


71

SUGGESTION #1 : Standard Indexing CREATE TABLE mytable ( id int not null auto_increment, myfield varchar(255) not null, primary key (id), key (myfield) ); If you index like this, you can either look for the whole string or do left-oriented LIKE searches SUGGESTION #2 : FULLTEXT Indexing CREATE TABLE mytable ( id int not null ...


70

normalizing the data like crazy Normalizing the data like crazy may not be the right strategy in this case. Keep your options open by storing the data both in the Normalized form and also in the form of materialized views highly suited to your application. Key in this type of applications is NOT writing adhoc queries. Query modeling is more important than ...


69

You should use: CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; Note that utf8_general_ci is no longer recommended best practice. See the related Q & A: What's the difference between utf8_general_ci and utf8_unicode_ci on Stack Overflow.


69

I previously addressed this issue in: Cannot open table mysql/innodb_index_stats These tables are created for you when you install MySQL 5.6. However, upgrading from MySQL 5.5 does not invoke the creation of these tables. Here are the scripts to create them manually: innodb_index_stats USE mysql; CREATE TABLE `innodb_index_stats` ( `database_name` ...


69

The only way MyISAM can be faster that InnoDB would be under this unique circumstance MyISAM When read, a MyISAM table's indexes can be read once from the .MYI file and loaded in the MyISAM Key Cache (as sized by key_buffer_size). How can you make a MyISAM table's .MYD faster to read? With this: ALTER TABLE mytable ROW_FORMAT=Fixed; I wrote about this in my ...


69

For the record SELECT * FROM mytable WHERE id IN (1,2,3,4) ORDER BY FIELD(id,3,2,1,4); should work as well because you do not have to order the list in the WHERE clause As for how it works, FIELD() is a function that returns the index position of a comma-delimited list if the value you are searching for exists. IF id = 1, then FIELD(id,3,2,1,4) returns ...


68

Firstly, find out your FOREIGN KEY constraint name in this way: SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, -- <<-- the one you want! REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'My_Table'; You can also add (to the WHERE clause) if you have more than one ...


58

One of the silent killers of MySQL Connections is the MySQL Packet. First, let's figure out what a MySQL Packet is. According to the page 99 of "Understanding MySQL Internals" (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining MySQL Packets: MySQL network communication code was written under the assumption that queries are always reasonably ...


58

Export: mysqldump -u username –-password=your_password database_name > file.sql Import: mysql -u username –-password=your_password database_name < file.sql


55

Based on this blog post http://ilikekillnerds.com/2014/08/fixing-sequel-pro-sql-encoding-error-for-imported-sql-files/ When importing your database dump, you need to select Western (Mac OS Roman) as the encoding format for the file to import without issue.


55

Open terminal and edit /etc/mysql/my.cnf sudo nano /etc/mysql/my.cnf Underneath the [mysqld] section.add: lower_case_table_names = 1 Restart mysql sudo /etc/init.d/mysql restart Then check it here: mysqladmin -u root -p variables


53

Is there a construct in SQL that would allow me to do something like the following: Yes, there is, almost exactly as you wrote it. Just put col1, col2 inside parentheses: -- works in PostgreSQL, Oracle, MySQL, DB2, HSQLDB SELECT whatever FROM t --- you missed the FROM WHERE (col1, col2) --- parentheses ...


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