540
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....
268
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 ...
84
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
...
82
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 ...
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
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 |
+-----+------+-----------+-------------------+...
66
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 ...
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 ...
57
You can let mysqldump create the dump in such a way that it does not create or select the database.
EXAMPLE : You are dumping the database db1 and loading it into database db2
This will put in the CREATE DATABASE and the USE commands in the dump
mysqldump -u... -p... --routines --triggers --databases db1 > /root/db1.sql
This will not put in the ...
52
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.
51
mysqldump has the --where option to execute a WHERE clause for a given table.
Although it is not possible to mysqldump a join query, you can export specific rows from each table so that every row fetched from each table will be involved in the join later on.
For your given query, you would need to mysqldump three times:
First, mysqldump all table3 rows ...
49
Export:
mysqldump -u username –-password=your_password database_name > file.sql
Import:
mysql -u username –-password=your_password database_name < file.sql
35
Easier to use the --single-transaction switch:
mysqldump --single-transaction -u username -p db > db.sql
33
What I think is that the database you are trying to dump contains procedures/methods that were defined by a user while logged in as root@'foobar'.
Now the solution is that you have to replace the definer's for that procedures/methods
then you can generate the dump without the error.
you can do this like ..
UPDATE `mysql`.`proc` p SET definer = 'root@...
33
You need to collect all the database names into a space delimited list. Use that for mysqldump
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
...
32
There are options to help you in this:
--insert-ignore Insert rows with INSERT IGNORE.
--replace Use REPLACE INTO instead of INSERT INTO.
-t, --no-create-info
Don't write table creation info.
Keep this paradigm in mind
mysqldump everything from DB1 into DUMP1
load DUMP1 into DB3
mysqldump everything from DB2 using ...
29
When you have more than a few tables it is much better running something like this:
mysql databasename -u [user] -p[password] -e 'show tables like "table_name_%"'
| grep -v Tables_in
| xargs mysqldump [databasename] -u [root] -p [password] > [target_file]
Or somethink like this:
mysqldump -u [user] -p[password] databasename `echo "show ...
28
You can also import an .sql file as an already connected user to the database manner :
mysql> use your_database_name;
mysql> source file.sql;
28
Please run this query:
SELECT
Data_BB / POWER(1024,1) Data_KB,
Data_BB / POWER(1024,2) Data_MB,
Data_BB / POWER(1024,3) Data_GB
FROM (SELECT SUM(data_length) Data_BB FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','performance_schema','mysql')) A;
This will give you a ballpark figure. The column index_length is ...
28
Best Practices to take MySQL server backup:
MySQL Replication
Setup Replication in MySQL. You will have to setup Master and Slave server. All read-writes to the DB could go to your Slave Server.
Advantage of having Replication is you can take a backup from your slave server without interrupting Master server, Your application will continue to work on ...
28
You are going to find this shocking, but you only need one major option : --opt
What is --opt ?
This option, enabled by default, is shorthand for the combination of --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It gives a fast dump operation and produces a dump file that can be ...
27
If you've already started the import, you can execute this command in another window to see the current size of your databases. This can be helpful if you know the total size of the .sql file you're importing.
SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MiB"
FROM information_schema.TABLES GROUP ...
24
I know it's really late, but I found this question when I faced the same problem. So, in case anyone need, I found two possible solutions:
your user misses the LOCK privilege, so you should ask your database administrator to grant it to you
run the same mysqldump command, simply adding the --single-transaction flag, eg. mysqldump --single-transaction -u ...
22
The recommended way to back up RDS is with automatic backups and DB snapshots. DB snapshots are basically the same as EBS snapshots, which are stored in S3 behind the scenes, but are only available within the same region.
If you need cross-region fault tolerance (good plan!), there is no way to restore your data in another region without doing it "the ...
22
Take these some points in your consideration they may help you in case of generating the dump and restoring it.
Use Extended inserts in dumps.
Dump with --tab format so you can use mysqlimport, which is faster than mysql < dumpfile.
Import with multiple threads, one for each table.
Use a different database engine if possible. importing into a heavily ...
20
No mysqldump -all-databases does not include all objects
mysqldump --help
-A, --all-databases Dump all the databases. This will be same as --databases
with all databases selected.
So mysqldump with --all-databases only dumps all the databases.
In order to migrate all the databases to a new server, you should take a complete ...
19
You can dump database with SequelPro (Mac OSX) with File > Export (⇧⌘E) option.
18
The reference to --binary-mode (introduced in MySQL 5.6.3) is probably a distraction.
It doesn't sound like you're dealing with a mysqldump output file, there. Try the file utility.
shell> file dumpfile.sql
dumpfile.sql: ASCII text
If you don't get the ASCII text response, you're dealing with either something that isn't a dump file from mysqldump at ...
17
The quickest solution would just be to re-create the definer so it does exist, as long as it doesn't create any conflicts with existing users.
CREATE USER 'root'@'foobar';
17
When you execute a mysqldump of a single database, all tables are dumped in alphabetical order.
Naturally, the reload of the mysqldump into a database would also be in alphabetical order.
You could just do a SHOW PROCESSLIST; and find out the DB Connection running the mysqldump. When the dump is reloaded, the DB Connection will vanish.
If you want to know ...
Only top voted, non community-wiki answers of a minimum length are eligible
