By default, the mysqldump utility, which allows to back a MySQL database, will perform a lock on all tables until the backup is complete.
mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the --single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions.
If mysqldump is not identified by the cmd prompt that means it cannot recognize where the mysqldump.exe is located. You need to add path of the directory where the exe is located in the PATH variable under environment variables. After doing that your command will start working in the cmd prompt.
Since you are using Ubuntu, all you need to do is just to add a file in your home directory and it will disable the mysqldump password prompting. This is done by creating the file ~/.my.cnf
(permissions need to be 600).
Add this to the .my.cnf
file
[mysqldump]
user=mysqluser
password=secret
This lets you connect as a MySQL user who requires a password without having to actually enter the password. You don't even need the -p or --password.
Very handy for scripting mysql & mysqldump commands.
The steps to achieve this can be found in this link.
Alternatively, you could use the following command:
mysqldump -u [user name] -p[password] [database name] > [dump file]
but be aware that it is inherently insecure, as the entire command (including password) can be viewed by any other user on the system while the dump is running, with a simple ps ax
command.
Adding to @Frankline's answer:
The -p
option must be excluded from the command in order to use the password in the config file.
Correct: mysqldump –u my_username my_db > my_db.sql
Wrong: mysqldump –u my_username -p my_db > my_db.sql
.my.cnf
can omit the username.
[mysqldump]
password=my_password
If your .my.cnf
file is not in a default location and mysqldump
doesn't see it, specify it using --defaults-file
.
mysqldump --defaults-file=/path-to-file/.my.cnf –u my_username my_db > my_db.sql
A few answers mention putting the password in a configuration file.
Alternatively, from your script you can export MYSQL_PWD=yourverysecretpassword
.
The upside of this method over using a configuration file is that you do not need a separate configuration file to keep in sync with your script. You only have the script to maintain.
There is no downside to this method.
The password is not visible to other users on the system (it would be visible if it is on the command line). The environment variables are only visible to the user running the mysql command, and root.
The password will also be visible to anyone who can read the script itself, so make sure the script itself is protected. This is in no way different than protecting a configuration file. You can still source the password from a separate file if you want to have the script publicly readable (export MYSQL_PWD=$(cat /root/mysql_password)
for example). It is still easier to export a variable than to build a configuration file.
E.g.,
$ export MYSQL_PWD=$(>&2 read -s -p "Input password (will not echo): "; echo "$REPLY")
$ mysqldump -u root mysql | head
-- MySQL dump 10.13 Distrib 5.6.23, for Linux (x86_64)
--
-- Host: localhost Database: mysql
-- ------------------------------------------------------
-- Server version 5.6.23
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
MariaDB documents the use of MYSQL_PWD
as:
Default password when connecting to mysqld. It is strongly recommended to use a more secure method of sending the password to the server.
The page has no mentions of what a "more secure" method may be.
This method is still supported in the latest documented version of MySQL: https://dev.mysql.com/doc/refman/8.0/en/environment-variables.html though it comes with the following warning:
Use of
MYSQL_PWD
to specify a MySQL password must be considered extremely insecure and should not be used. Some versions of ps include an option to display the environment of running processes. On some systems, if you setMYSQL_PWD
, your password is exposed to any other user who runs ps. Even on systems without such a version of ps, it is unwise to assume that there are no other methods by which users can examine process environments.
The security of environment variables is covered in much details at https://security.stackexchange.com/a/14009/10002 and this answer also addresses the concerns mentioned in the comments. TL;DR Irrelevant for over a decade.
Having said that, the MySQL documentation also warns:
MYSQL_PWD
is deprecated as of MySQL 8.0; expect it to be removed in a future version of MySQL.
To which I'll leave you with maxschlepzig's comment from below:
funny though how Oracle doesn't deprecate passing the password on the command line which in fact is extremely insecure
To use a file that is anywhere inside of OS, use --defaults-extra-file
eg:
mysqldump --defaults-extra-file=/path/.sqlpwd [database] > [desiredoutput].sql
Note: .sqlpwd
is just an example filename. You can use whatever you desire.
Note: MySQL will automatically check for ~/.my.cnf
which can be used instead of --defaults-extra-file
If your using CRON like me, try this!
mysqldump --defaults-extra-file=/path/.sqlpwd [database] > "$(date '+%F').sql"
Required Permission and Recommended Ownership
sudo chmod 600 /path/.sqlpwd && sudo chown $USER:nogroup /path/.sqlpwd
.sqlpwd
contents:
[mysqldump]
user=username
password=password
Other examples to pass in .cnf
or .sqlpwd
[mysql]
user=username
password=password
[mysqldiff]
user=username
password=password
[client]
user=username
password=password
If you wanted to log into a database automatically, you would need the [mysql]
entry for instance.
You could now make an alias that auto connects you to DB
alias whateveryouwant="mysql --defaults-extra-file=/path/.sqlpwd [database]"
You can also only put the password inside .sqlpwd
and pass the username via the script/cli. I'm not sure if this would improve security or not, that would be a different question all-together.
For completeness sake I will state you can do the following, but is extremely insecure and should never be used in a production environment:
mysqldump -u [user_name] -p[password] [database] > [desiredoutput].sql
Note: There is NO SPACE between -p and the password.
Eg -pPassWord
is correct while -p Password
is incorrect.
Yeah it is very easy .... just in one magical command line no more
mysqldump --user='myusername' --password='mypassword' -h MyUrlOrIPAddress databasename > myfile.sql
and done :)
For me, using MariaDB I had to do this: Add the file ~/.my.cnf
and change permissions by doing chmod 600 ~/.my.cnf
. Then add your credentials to the file. The magic piece I was missing was that the password needs to be under the client block (ref: docs), like so:
[client]
password = "my_password"
[mysqldump]
user = root
host = localhost
If you happen to come here looking for how to do a mysqldump with MariaDB. Place the password under a [client] block, and then the user under a [mysqldump] block.
Here is a solution for Docker in a script /bin/sh :
docker exec [MYSQL_CONTAINER_NAME] sh -c 'exec echo "[client]" > /root/mysql-credentials.cnf'
docker exec [MYSQL_CONTAINER_NAME] sh -c 'exec echo "user=root" >> /root/mysql-credentials.cnf'
docker exec [MYSQL_CONTAINER_NAME] sh -c 'exec echo "password=$MYSQL_ROOT_PASSWORD" >> /root/mysql-credentials.cnf'
docker exec [MYSQL_CONTAINER_NAME] sh -c 'exec mysqldump --defaults-extra-file=/root/mysql-credentials.cnf --all-databases'
Replace [MYSQL_CONTAINER_NAME]
and be sure that the environment variable MYSQL_ROOT_PASSWORD
is set in your container.
Hope it will help you like it could help me !
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With