315
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 ...
113
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
...
83
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 |
+-----+------+-----------+-------------------+...
83
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-...
59
use --no-tablespaces
see mysqldump-Documentation
mysqldump requires at least the SELECT privilege for dumped tables,
SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES
if the --single-transaction option is not used, and (as of MySQL
8.0.21) PROCESS if the --no-tablespaces option is not used.
Certain options might require other privileges ...
57
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.
42
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 ...
39
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"
...
38
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;
36
Easier to use the --single-transaction switch:
mysqldump --single-transaction -u username -p db > db.sql
33
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 ...
33
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 ...
31
tl;dr Breaking change introduced in minor MySQL update, use --no-tablespaces option in mysqldump from now on (recommended) or add the global PROCESS privilege to the user running the command.
I experienced the same issue on some of my machines. Not all at once, not on all commands, not on all users.
Why not all at once?
Turns out this is a breaking change ...
30
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 ...
30
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 Master ...
24
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 ...
21
You have to use the mysql client to reload
mysql -u root -p -Ddatabasename < /home/databasename_bkup.sql
Another way to reload would be
mysql -u root -p -Ddatabasename
then from the MySQL prompt, do this
mysql> source /home/databasename_bkup.sql
If you would like the mysqldump to drop and recreate the database for you, create the dump like ...
21
Since I happened to be researching this myself, here's a summary of what I found.
According to a 2015 blog post from the MySQL dev team, the main advantages of mysqlpump are that it can use multiple threads in parallel to speed up the dumping and that it doesn't share mysqldump's backwards compatibility requirements, which should open the door for further ...
20
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 ...
16
You can do it simply using below command:
mysqldump -uusername -ppassword dbname \
--ignore-table=schema.tablename1 \
--ignore-table=schema.tablename2 \
--ignore-table=schema.tablename3 > mysqldump.sql
16
Back on Dec 16, 2011, I answered the question How do you mysqldump specific table(s)?
I collected all tables not including a certain set of table names.
Using the same principles, you could have collect all the database names from the metadata table information_schema.schemata that you want mysqldump'd, create a query to return that list, then use that ...
16
Use grep to exclude databases you don't want:
candidates=$(echo "show databases" | mysql | grep -Ev "^(Database|mysql|performance_schema|information_schema)$")
mysqldump --databases $candidates
From looking at https://stackoverflow.com/questions/19354870/bash-command-line-and-input-limit it seems like you'll be able to handle long lines. Otherwise you can ...
15
Try this:
SELECT column1, column2, column10
FROM USER
INTO OUTFILE "c:/user.csv"
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY "\n";
For more option read the documentation page.
14
I could easily suggest changing InnoDB settings which might be a littel heavy-handed just to get a mysqldump to work. You may not like what I am about the suggest, but I believe it's your best (only) option. Here it goes:
SUGGESTION #1 : Disable extended inserts
The default setting for mysqldump would include clumping together hundreds or thousands of rows ...
14
I have also been looking into the differences between these two utilities, and in addition to what is mentioned in @Neil's answer, it seems that mysqlpump also has fewer options than mysqldump, such as not allowing for the following:
--compatible=
--tab, and its related options of:
--fields-terminated-by=
--fields-enclosed-by=
--fields-optionally-enclosed-...
13
Do you really need the entire database to be restored? If you don't, my 2c:
You can extract specific tables to do your restore on "chunks". Something like this:
zcat your-dump.gz.sql | sed -n -e '/DROP TABLE.*`TABLE_NAME`/,/UNLOCK TABLES/p' > table_name-dump.sql
I did it once and it took like 10 minutes to extract the table I needed - my full restore ...
12
Every 2 seconds you will see the processes running.
watch 'echo "show processlist;" | mysql -uuser -ppassword';
If you want it less frequent then add -n x where x is the number of seconds. 5 seconds would be:
watch -n 5 'echo "show processlist;" | mysql -uuser -ppassword';
12
The mysqldump, by default, will drop the table. You should specified the --no-create-info option like this:
mysqldump -u... -p... --no-create-info --skip-extended-insert mydb t1 > mydb_table.sql
That way, you only have inserts to deal with. Using --skip-extended-insert will insert one row at a time. This help deal with duplicate issues, but you will ...
12
ASPECT #1 : Replication
I don't think that
replicate-wild-do-table = db_backup.%
replicate-rewrite-db = db->db_backup
belong together.
Other people have wondered about this as well
Jun 14, 2012 : Configured MySQL replication but its not working
Jun 13, 2012 : What is the difference between 'replicate-rewrite-db' and 'replicate-do-db ' during mysql ...
Only top voted, non community-wiki answers of a minimum length are eligible
