The mysqldump command in MySQL is used to create backups of MySQL databases. One of the important options available is -h, --host=name, which specifies the MySQL server hostname or IP address to connect to during the dump process.
Here are several examples demonstrating the usage of -h, --host=name in mysqldump:
Example 1: Connecting to a MySQL server with hostname localhost:
mysqldump -h localhost -u username -p database_name > backup.sql
This command connects to the MySQL server running on localhost and dumps the database_name into a file named backup.sql. To verify if the command executed successfully, check if the backup.sql file exists and contains the expected SQL dump data.
Example 2: Connecting to a MySQL server with IP address 192.168.1.100:
mysqldump -h 192.168.1.100 -u username -p database_name > backup.sql
This command connects to a MySQL server at IP address 192.168.1.100 and dumps the database_name into backup.sql. Verify by checking the existence and content of backup.sql.
Example 3: Using the long option --host to specify a remote server:
mysqldump --host=remote.example.com -u username -p database_name > backup.sql
Here, the command connects to a MySQL server at remote.example.com and dumps database_name into backup.sql. Verify as before.
Example 4: Using an IPv6 address to connect to a MySQL server:
mysqldump -h 2001:db8:85a3:0:0:8a2e:370:7334 -u username -p database_name > backup.sql
This command connects to a MySQL server using an IPv6 address and dumps the specified database into backup.sql. Check the file backup.sql for the dumped data to verify.
Example 5: Connecting to a MySQL server on a non-default port (e.g., 3307):
mysqldump -h localhost -P 3307 -u username -p database_name > backup.sql
This command connects to a MySQL server on localhost but specifies port 3307 and dumps the database into backup.sql. Verify the content of backup.sql after execution.
Example 6: Connecting to a MySQL server using a Unix socket file:
mysqldump --host=/var/run/mysqld/mysqld.sock -u username -p database_name > backup.sql
This command connects to MySQL using a Unix socket file located at /var/run/mysqld/mysqld.sock and dumps the database into backup.sql. Check backup.sql after running to verify.
Example 7: Using a fully qualified domain name (FQDN) for the host:
mysqldump --host=db.example.com -u username -p database_name > backup.sql
Connects to the MySQL server at db.example.com and dumps the specified database into backup.sql. Ensure backup.sql is created and contains the database dump.
Example 8: Connecting to a MySQL server with SSL encryption:
mysqldump --host=secure.example.com --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -u username -p database_name > backup.sql
This command connects securely to secure.example.com using SSL certificates and dumps the database into backup.sql. Verify the dump file after execution.
Example 9: Connecting to a MySQL server using an alternative authentication plugin:
mysqldump --host=mysql.example.com --default-auth=mysql_native_password -u username -p database_name > backup.sql
This command connects to mysql.example.com using the MySQL native password authentication plugin and dumps the specified database into backup.sql. Check the file for the dumped data.
Example 10: Using a host alias configured in ~/.my.cnf:
mysqldump --host=production -u username -p database_name > backup.sql
Assuming production is an alias configured in ~/.my.cnf file, this command connects to the MySQL server defined by that alias and dumps the database into backup.sql. Verify the file post-execution.
Also check similar articles.
Dumping BLOBs in Hexadecimal Format with mysqldump
Forcing mysqldump Execution Even on Errors
Emitting FLUSH PRIVILEGES Statement in mysqldump
Flushing Logs Before Dumping with mysqldump
Escaping Fields in mysqldump Output
Discussion about this post