# see also https://adrhc.go.ro/wordpress/mysql-command-line/

#CREATE SYSTEM DB (first step and mandatory before using mysql)
export SRVPATH=/ffp/opt/srv
mkdir -p $SRVPATH/mysql/innodb/
mkdir -p $SRVPATH/mysql/innodblogdir/
mkdir -p $SRVPATH/mysql/binlog/
mkdir -p $SRVPATH/mysql/log/
mkdir -p $SRVPATH/mysql/tmp/
mkdir -p $SRVPATH/mysql/data
cd /ffp
# use your my.cnf otherwise mysql won't create some innodb tables (see http://bugs.mysql.com/bug.php?id=67179&files=1):
scripts/mysql_install_db --user=root --datadir=/ffp/opt/srv/mysql/data --defaults-file=/ffp/etc/my.cnf
# if you don't have a my.cnf than run:
#scripts/mysql_install_db --user=root --datadir=/ffp/opt/srv/mysql/data
#Later (see mysql-five-tables-5.6.25.sql below) you'll create innodb missing tables.
cd ~ && /ffp/start/mysqld.sh start
mysql -p -> the default password is nothing
GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'xxx' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'root'@'localhost' IDENTIFIED BY 'xxx' WITH GRANT OPTION;
FLUSH PRIVILEGES;
mysqladmin -u root password "xxx"

#1. create databases and grant privileges
mysql -p
CREATE DATABASE exifweb CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE DATABASE wordpress CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE DATABASE owncloud702 CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE DATABASE ghost CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE DATABASE pydio CHARACTER SET utf8 COLLATE utf8_unicode_ci;
GRANT ALL ON exifweb.* TO 'exifweb'@'%' IDENTIFIED BY 'exifweb' WITH GRANT OPTION;
GRANT ALL ON wordpress.* TO 'wordpress'@'%' IDENTIFIED BY 'wordpress' WITH GRANT OPTION;
GRANT ALL ON owncloud702.* TO 'owncloud702'@'%' IDENTIFIED BY 'owncloud702' WITH GRANT OPTION;
GRANT ALL ON ghost.* TO 'ghost'@'%' IDENTIFIED BY 'ghost' WITH GRANT OPTION;
GRANT ALL ON pydio.* TO 'pydio'@'%' IDENTIFIED BY 'pydio' WITH GRANT OPTION;
FLUSH PRIVILEGES;

#2. prepare sql for databases to restore
cp /i-data/md0/seagate-ext4/ProjectsNew/nsa310-config/trunk/mysql-db-design/*.sql.gz $HOME/temp/mysql-restore
cd $HOME/temp/mysql-restore
gunzip exifweb.sql.gz
gunzip wordpress.sql.gz
gunzip owncloud702.sql.gz
gunzip ghost.sql.gz
gunzip pydio.sql.gz
ls -l *.sql

#3. restore DBs
# The config variable max_allowed_packet must be larger than the imported sql script!
#see http://bugs.mysql.com/bug.php?id=67179&files=1
# Take five-tables.sql from me (it's a copy of the original):
wget https://adrhc.go.ro/public/mysql-five-tables-5.6.25.sql
# or take it from the original post:
#wget http://bugs.mysql.com/file.php?id=19725&bug_id=67179
mv 'file.php?id=19725&bug_id=67179' mysql-five-tables-5.6.25.sql
# this solves the innodb missing tables problem:
mysql -p mysql < mysql-five-tables-5.6.25.sql
# now import your databases:
mysql -u root -p exifweb < nsa310-config-trunk/mysql-db-design/exifweb.sql
mysql -u root -p wordpress < nsa310-config-trunk/mysql-db-design/wordpress.sql
mysql -u root -p owncloud702 < nsa310-config-trunk/mysql-db-design/owncloud702.sql
mysql -u root -p ghost < ghost.sql
mysql -u root -p pydio < pydio.sql
mysqlcheck -A --password=xxx --auto-repair

#UPGRADE DB schema
#If you use InnoDB, consider setting innodb_fast_shutdown to 0 before shutting down and upgrading your server.
/ffp/start/mysqld.sh stop
sed -i s/"innodb_fast_shutdown\s*=\s*1"/"innodb_fast_shutdown = 0"/ /ffp/etc/my.cnf
grep innodb_fast_shutdown /ffp/etc/my.cnf
/ffp/start/mysqld.sh start
/ffp/start/mysqld.sh stop
/ffp/start/mysqld.sh start
mysql_upgrade -u root -p --socket=/ffp/var/run/mysql/mysql.sock -v
/ffp/start/mysqld.sh stop
sed -i s/"innodb_fast_shutdown\s*=\s*0"/"innodb_fast_shutdown = 1"/ /ffp/etc/my.cnf
grep innodb_fast_shutdown /ffp/etc/my.cnf
/ffp/start/mysqld.sh start