Daily Archives: 22 April 2016

Copy all databases to another host

 in Bash / GNU/Linux  tagged backup / export / import / mysql / mysqldump by

The following command will use mysqldump to create a dump of all available databases in the OLD_HOST that are available for the user OLD_USER.
The results will be imported to another server via the mysql pipe.

OLD_USER="myUser"; OLD_PASS="myPASS"; OLD_HOST="myHost";
NEW_USER="myUserNEW"; NEW_PASS="myPASSNEW"; NEW_HOST="myHostNEW";
mysqldump -u "$OLD_USER" -p"$OLD_PASS" -h "$OLD_HOST" --all-databases | mysql -h "$NEW_HOST" -u "$NEW_USER" -p"$NEW_PASS";

The user must have the LOCK TABLES privilege for the above command to work or else you will get the following error.

mysqldump: Got error: 1044: "Access denied for user 'OLD_USER'@'OLD_HOST' to database 'DBNAME'" when using LOCK TABLES

In case you cannot give the privilege to the user, then use the parameter --single-transaction to mitigate the problem. The command changes as follows.

OLD_USER="myUser"; OLD_PASS="myPASS"; OLD_HOST="myHost";
NEW_USER="myUserNEW"; NEW_PASS="myPASSNEW"; NEW_HOST="myHostNEW";
mysqldump -u "$OLD_USER" -p"$OLD_PASS" -h "$OLD_HOST" --single-transaction --all-databases | mysql -h "$NEW_HOST" -u "$NEW_USER" -p"$NEW_PASS";

In case you want to copy only specific databases, use the following

OLD_USER="myUser"; OLD_PASS="myPASS"; OLD_HOST="myHost"; OLD_DBS=("DB1" "DB2");
NEW_USER="myUserNEW"; NEW_PASS="myPASSNEW"; NEW_HOST="myHostNEW";
mysqldump -u "$OLD_USER" -p"$OLD_PASS" -h "$OLD_HOST" "${OLD_DBS[@]}" | mysql -h "$NEW_HOST" -u "$NEW_USER" -p"$NEW_PASS";

In case you want to copy only specific tables from a database, use the following

OLD_USER="myUser"; OLD_PASS="myPASS"; OLD_HOST="myHost"; OLD_DB="DB1"; OLD_TABLES=("TBL1" "TBL2");
NEW_USER="myUserNEW"; NEW_PASS="myPASSNEW"; NEW_HOST="myHostNEW"; NEW_DB="NewDB";
mysqldump -u "$OLD_USER" -p"$OLD_PASS" -h "$OLD_HOST" "$OLD_DB" "${OLD_TABLES[@]}" | mysql -h "$NEW_HOST" -u "$NEW_USER" -p"$NEW_PASS" "$NEW_DB";

Export/Backup all MySQL databases

 in Bash / GNU/Linux  tagged backup / export / mysqldump by

The following command will use mysqldump to create a dump of all available databases in the HOST that are available for the user USER.
The results will be found in a file that begins with the current date and will contain the hostname as part of the name.

USER="myUser"; PASS="myPASS"; HOST="myHost"; mysqldump -u "$USER" -p"$PASS" -h "$HOST" --all-databases > "`date +%F`-backup-all.$HOST.sql"

The user must have the LOCK TABLES privilege for the above command to work or else you will get the following error.

mysqldump: Got error: 1044: "Access denied for user 'USER'@'HOST' to database 'DBNAME'" when using LOCK TABLES

In case you cannot give the privilege to the user, then use the parameter --single-transaction to mitigate the problem. The command changes as follows.

USER="myUser"; PASS="myPASS"; HOST="myHost"; mysqldump -u "$USER" -p"$PASS" -h "$HOST" --single-transaction --all-databases > "`date +%F`-backup-all.$HOST.sql"

Bash Function to print out the files and the lines that contain a needle

 in Bash  tagged bash / color / find / function by

The following code will create a function in bash that accepts two parameters (1: the place to search in, 2: the value to search for).

You can place it in your ~/.bashrc file to have it available whenever you open a bash shell.

#1. Copy/paste the below lines in your .bashrc

#takes 2 parameters (1: the haystack to search in, 2: the needle)
# Will print out the files and the lines that contain the needle
xfind(){
  FIND_VAR="$2";
  STACK="$1";
  if [ -f "$STACK" ] || [ -d "$STACK" ]; then
    find "$STACK" \
      -exec grep --color "$FIND_VAR" -sl '{}' \; \
      -exec grep "$FIND_VAR" -s '{}' \;
  else
    echo "ERROR: No file or folder with the name '$STACK' exist";
  fi
}
#2. Run source ~/.bashrc -- to reload

Usage examples:

xfind . "bar";
xfind /etc/ "conf";

Recursively change extension of multiple files using find

 in Bash / GNU/Linux  tagged find / rename by

Assuming you have a whole bunch of files that you need to change their extension from one to another, you can use the following commands after setting the values for the BEFORE and the AFTER variables to the values you need.

BEFORE='.txt'; AFTER='.csv'; find . -type f -name "*$BEFORE" -exec bash -c 'mv "$1" "${1%$2}$3"' _ '{}' "$BEFORE" "$AFTER" \;

What the above will do is: after setting the two input variables it will call find in the current directory (with recursion) and find all files that their suffix is the value you set in BEFORE. Then, for each match it will create a new shell terminal in which it will rename the file by removing the old suffix and then attaching the new one. We pass the input variables as parameters to the new shell and that is why inside the code of the shell we are using variables $1, $2 and $3. The reason we had to issue a new shell is because we wanted to reuse the ‘{}’ variable.