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
