If you have a schema already in place, you should use mysqldump options to create the dump of the data to perform in the inserts only
mysqldump -h... -u... -p... --no-create-info --databases ... > MySQLData.sql
You should also make sure to raise the bulk_insert_buffer_size (default is 8M) on all DB Servers to accommodate large extended inserts. This will also help LOAD DATA INFILE if loading non-empty tables.
You are going to have to also adjust your max_allowed_packet (default is 1M).
Try these settings for starters:
[mysqld]
max_allowed_packet=256M
bulk_insert_buffer_size=256M
UPDATE 2011-10-11 06:53 EDT
Regardless of how beefy the hardware is, how much RAM you have allocated, how well tuned the OS is, and how current the version of MySQL is, MySQL will only perform as well as it is configured.
Example: If you have a swimming pool that can hold 10,000 gallons of water, you have a truck holding 10,000 gallons of water, and the hose on the truck is only as big as a straw, you can only push but so much water through the straw to fill the pool. Getting a bigger truck or getting a faster water pump simply will not improve things. You must exchange the hose for a much bigger hose to accommodate more water, thus, more throughput.
In like fashion, MySQL 5.5 out-of-the-box does not come fully tuned.
Example #1: MySQL 5.5 comes with semisynchronous replication. By default, it is disabled. You must do the two step process of starting mysql, runnning INSTALL PLUGIN on master and slave modules, shutdown mysql, add timing and activation options to my.cnf for the semisych features, then starting up mysql for the second time. Only then will semisync replication work.
Example #2: The bulk insert buffer by default is 8M. The bulk insert buffer will not grow because of the presence of any specific hardware or software. It stays 8M until you increase it. It is possible to increase it by either adding it to my.cnf and restarting mysql or running SET bulk_insert_buffer_size = 268435456;
to set it to 256M within a session and then load the mysqldump within that same session.
Example #3: MySQL 5.5 is fully capable of engaging multiple CPUs. By default, the features for engaging multiple CPUs is disabled. They require tuning because although MySQL 5.5 is multicore ready, MySQL 5.5 is only as multithreaded as you configure it.
Conclusion: You must configure MySQL to recognize that it has beefy hardware, more RAM, and a cooperative OS at its disposal.
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 |
+-----+------+-----------+-------------------+---------+------+-------+------------------+
| 174 | root | localhost | example | Sleep | 297 | | |
| 407 | root | localhost | | Query | 0 | | show processlist |
+-----+------+-----------+-------------------+---------+------+-------+------------------+
The query with id 174 is the one blocking deletion of the 'example' database, so before you kill any processes first let MySQL try to terminate the query:
$ mysqladmin kill 174
Run the processlist
command above again to confirm that it was killed.
If this doesn't work, then you could perhaps look at killing the errant process, but before that you might try restarting the MySQL server.
You can also run commands like 'SHOW FULL PROCESSLIST' and 'KILL 174' in the MySQL shell, for example if you only have the MySQL client installed. The main point is to avoid killing the process using 'kill' in the shell unless absolutely necessary.
Generally speaking you can use either mysql
or mysqladmin
. You shouldn't need to be running commands like this that often though; once you start killing queries regularly something is definitely wrong and you'd be better off fixing that problem (killing the query process is just treating the symptom).
Best Answer
It's a best practise that first to upgrade MySQL 5.6 to MySQL 5.7, then from MySQL 5.7 to MySQL 8.0.
Please go through MySQL 8.0 changelog to take changes in your database for deprecated & removed features otherwise upgrade end up in so many errors
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/
Before, upgrading to MySQL 8.0, run upgrade checker trail using mysqlshell.
mysqlsh mysql_user@192.168.X.X:3306 -e "util.checkForServerUpgrade();" > upgrade_alert.txt
Final step for upgrade should be done logical only to avoid errors in inplace upgrade.