別サーバにpiwikデータを引っ越しします。

consoleコマンドを使う方法ではできなかったので、DBのインポートを使用。

移行元

$ mysqldump -upiwik -ppiwik_db_password piwik > /tmp/piwik.sql
$ scp /tmp/piwik.sql  username@targetserver.net:/tmp

移行先 piwikは稼働済

mysql> DROP DATABASE piwik;
mysql> CREATE DATABASE piwik;
$ mysql -upiwik -ppiwik_db_password piwik < /tmp/piwik.sql

Drupal設定変更

admin/config/system/piwik
https://piwik.taregetserver.net/

権限修正

$ mysql -u root -p --local-infile piwik
mysql>  LOAD DATA LOCAL INFILE '/var/www/html/piwik/tmp/assets/piwik_option-60f9c08f3494c0842db8516784f5b3d5.csv' REPLACE INTO TABLE `option` CHARACTER SET latin1 FIELDS TERMINATED BY ' ' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY ' ' (option_name,option_value) ;
$ sudo chmod o+rx /var/www/html/piwik/tmp/assets

cosoleコマンドを使う方法

MySQL接続許可設定およびユーザ作成@移行先サーバ

$ sudo vim /etc/mysql/my.cnf
#bind-address           = 127.0.0.1
$ sudo service mysql restart

mysql> GRANT ALL PRIVILEGES ON piwik.* TO 'piwik'@'octaviadata.com' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
mysql> SELECT * from information_schema.user_privileges where grantee like "'piwik'%";
+---------------------------+---------------+----------------+--------------+
| GRANTEE                   | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+---------------------------+---------------+----------------+--------------+
| 'piwik'@'localhost'       | def           | USAGE          | NO           |
| 'piwik'@'octaviadata.com' | def           | USAGE          | NO           |
+---------------------------+---------------+----------------+--------------+

piwikデータ移行コマンド@移行元サーバ

$ cd /var/www/html/piwik
$ sudo ./console migration:site 1 --db-host="targetserver.net" --db-name="piwik" --db-username="piwik" --db-prefix="piwik_"
Please provide the destination database password: PASSWORD
$ sudo ./console migration:site 2 --db-host="targetserver.net" --db-name="piwik" --db-username="piwik" --db-prefix="piwik_"
Please provide the destination database password: PASSWORD

MySQL接続ユーザの削除@移行先サーバ

mysql> DELETE FROM mysql.user WHERE User = 'piwik' AND Host = 'octaviadata.com';
mysql> FLUSH PRIVILEGES;
mysql> SELECT * from information_schema.user_privileges where grantee like "'piwik'%";
+---------------------+---------------+----------------+--------------+
| GRANTEE             | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+---------------------+---------------+----------------+--------------+
| 'piwik'@'localhost' | def           | USAGE          | NO           |
+---------------------+---------------+----------------+--------------+

$ sudo vim /etc/mysql/my.cnf
bind-address           = 127.0.0.1
$ sudo service mysql restart