mysql 定期備份

1.首先不可用script上加密碼，因此要先建造一個密碼路徑


mysql_config_editor set --login-path=aname --host=localhost --user=user --password


h=aname --host=localhost --user=user --password


其中、aname可自行命名!
注意: After entering the password : Use double quotes around the password !!

最後，採用下列方式代替登入

mysql --login-path=aname


採用 crontab 排程

In a command prompt type

sudo crontab -e

And add the following line at the bottom of the file:

30 23 * * * /path/to/mysqldump.sh

執行sh
1.使用 sh test.sh 來執行script檔案

2.新增執行的許可權：

$ chmod  x test.sh

新增完執行許可權之後，便可以使用 ./test.sh 來執行指令碼，

Add the following code to the file( mysqldump.sh) and save it:

#!/bin/bash
#----------------------------------------
# OPTIONS
#----------------------------------------
#USER='root'       # MySQL User
#PASSWORD='webdev' # MySQL Password
DAYS_TO_KEEP=0    # 0 to keep forever
GZIP=1            # 1 = Compress
BACKUP_PATH='/backups/mysql'
#----------------------------------------

# Create the backup folder
if [ ! -d $BACKUP_PATH ]; then
  mkdir -p $BACKUP_PATH
fi

# Get list of database names
databases=`mysql --login-path=aname -e "SHOW DATABASES;" | tr -d "|" | grep -v Database`

for db in $databases; do

  if [ $db == 'information_schema' ] || [ $db == 'performance_schema' ] || [ $db == 'mysql' ] || [ $db == 'sys' ]; then
    echo "Skipping database: $db"
    continue
  fi
  
  date=$(date -I)
  if [ "$GZIP" -eq 0 ] ; then
    echo "Backing up database: $db without compression"      
    mysqldump --login-path=aname --databases $db > $BACKUP_PATH/$date-$db.sql
  else
    echo "Backing up database: $db with compression"
    mysqldump --login-path=aname --databases $db | gzip -c > $BACKUP_PATH/$date-$db.gz
  fi
done

# Delete old backups
if [ "$DAYS_TO_KEEP" -gt 0 ] ; then
  echo "Deleting backups older than $DAYS_TO_KEEP days"
  find $BACKUP_PATH/* -mtime +$DAYS_TO_KEEP -exec rm {} \;
fi

mysqldump發生問題

mysqldump時發生: mysqldump: Got error: 1449: The user specified as a definer

1.新增user，很安全，但是有時很麻煩
2.確認user沒問題，變更definer

找出原本view的內容

Run this SQL to generate the necessary ALTER statements

SELECT CONCAT("ALTER DEFINER=`youruser`@`host` VIEW ", 
table_name, " AS ", view_definition, ";") 
FROM information_schema.views 
WHERE table_schema='your-database-name';

SELECT CONCAT("ALTER DEFINER=`your_username`@`127.0.0.1` VIEW ", table_name, " AS ", view_definition, ";") FROM information_schema.views WHERE table_schema='your-database-name'

然後執行sql的內容

google api 在中國無法使用的問題

1.
The Google Maps APIs are served within China from the domain maps.google.cn. This domain does not support https. When making requests to the Google Maps APIs from China, please replace https://maps.googleapis.com with http://maps.google.cn.
所以要改用 http://maps.google.cn

<script src="https://maps.googleapis.com/maps/api/js?key=[key]"></script>
<script>
    window.google || document.write('<script src="http://maps.google.cn/maps/api/js?key=[key]">\x3C/script>');
</script>

2.偵測ip&國家

$.getJSON("http://ip-api.com/json/", function (data) {
        var country = data.country_name;
        alert(country);        
    });

$.getJSON("http://freegeoip.net/json/", function (data) { 
     var country = data.country_name; 
     alert(country);
});