544
If you are dumping tables t1, t2, and t3 from mydb
mysqldump -u... -p... mydb t1 t2 t3 > mydb_tables.sql
If you have a ton of tables in mydb and you want to dump everything except t1, t2, and t3, do this:
DBTODUMP=mydb
SQL="SET group_concat_max_len = 10240;"
SQL="${SQL} SELECT GROUP_CONCAT(table_name separator ' ')"
SQL="${SQL} FROM information_schema....
270
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 ...
256
Your innodb_buffer_pool_size is enormous. You have it set at 20971520000. That's 19.5135 GB. If you only 5GB of InnoDB data and indexes, then you should only have about 8GB. Even this may 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) ...
238
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 ...
141
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 ...
139
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 ...
139
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;
125
I actually discussed innodb_thread_concurrency with a MySQL Expert at the Percona Live NYC conference back in May 2011.
I learned something surprising: In spite of the documentation, it is best to leave innodb_thread_concurrency at 0 (infinite concurrency). That way, InnoDB decides the best number of innodb_concurrency_tickets to open for a given MySQL ...
115
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 ...
108
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, ...
105
Keep in mind the busiest file in the InnoDB infrastructure is /var/lib/mysql/ibdata1
This file normally houses many classes of information (when innodb_file_per_table is 0)
Table Data
Table Indexes
MVCC (Multiversioning Concurrency Control) Data
Rollbacks Segments
Undo Tablespace
Table Metadata
See Pictorial Representation
Many people create multiple ...
93
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 ...
85
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
...
84
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 ...
81
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 ...
79
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.
75
Reposting my answer to a similar question regarding SQL Server:
In the SQL world, order is not an inherent property of a set of data.
Thus, you get no guarantees from your RDBMS that your data will come
back in a certain order -- or even in a consistent order -- unless you
query your data with an ORDER BY clause.
So, to answer your question:
MySQL ...
73
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 |
+-----+------+-----------+-------------------+...
72
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-...
72
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 ...
71
I found a very bizarre but informative article about 8 reasons why one should not use ENUM.
Even without the article, I know
there is no easy method for adding new values, some techniques are very high risk
numbers should never be used
only use strings (@DTest already mentioned this in his answer)
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
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 ...
68
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 ...
67
The architecture of InnoDB demands the use of four basic types of info pages
Table Data Pages
Table Index Pages
Table MetaData
MVCC Data (to support Transaction Isolation and ACID Compliance)
Rollback Segments
Undo Space
Double Write Buffer (background writing to prevent reliance on OS caching)
Insert Buffer (managing changes to non-unique secondary ...
67
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` ...
65
I recently moved a 30GB database with the following stragegy:
Old Server
Stop mysql server
Copy contents of datadir to another location on disk (~/mysqldata/*)
Start mysql server again (downtime was 10-15 minutes)
compress the data (tar -czvf mysqldata.tar.gz ~/mysqldata)
copy the compressed file to new server
New Server
install mysql (don't start)
unzip ...
64
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 ...
64
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.
64
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 ...
Only top voted, non community-wiki answers of a minimum length are eligible
