{"id":1975,"date":"2015-06-08T22:24:01","date_gmt":"2015-06-08T20:24:01","guid":{"rendered":"https:\/\/adrhc.go.ro\/wordpress\/?p=1975"},"modified":"2019-03-29T21:55:21","modified_gmt":"2019-03-29T19:55:21","slug":"create-mysql-system-db","status":"publish","type":"post","link":"https:\/\/adrhc.go.ro\/blog\/create-mysql-system-db\/","title":{"rendered":"Create mysql system db"},"content":{"rendered":"<pre class=\"brush:bash shell\">\r\n# see also https:\/\/adrhc.go.ro\/wordpress\/mysql-command-line\/\r\n\r\n#CREATE SYSTEM DB (first step and mandatory before using mysql)\r\nexport SRVPATH=\/ffp\/opt\/srv\r\nmkdir -p $SRVPATH\/mysql\/innodb\/\r\nmkdir -p $SRVPATH\/mysql\/innodblogdir\/\r\nmkdir -p $SRVPATH\/mysql\/binlog\/\r\nmkdir -p $SRVPATH\/mysql\/log\/\r\nmkdir -p $SRVPATH\/mysql\/tmp\/\r\nmkdir -p $SRVPATH\/mysql\/data\r\ncd \/ffp\r\n# use your my.cnf otherwise mysql won't create some innodb tables (see http:\/\/bugs.mysql.com\/bug.php?id=67179&files=1):\r\nscripts\/mysql_install_db --user=root --datadir=\/ffp\/opt\/srv\/mysql\/data --defaults-file=\/ffp\/etc\/my.cnf\r\n# if you don't have a my.cnf than run:\r\n#scripts\/mysql_install_db --user=root --datadir=\/ffp\/opt\/srv\/mysql\/data\r\n#Later (see mysql-five-tables-5.6.25.sql below) you'll create innodb missing tables.\r\ncd ~ && \/ffp\/start\/mysqld.sh start\r\nmysql -p -> the default password is nothing\r\nGRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'xxx' WITH GRANT OPTION;\r\nGRANT ALL ON *.* TO 'root'@'localhost' IDENTIFIED BY 'xxx' WITH GRANT OPTION;\r\nFLUSH PRIVILEGES;\r\nmysqladmin -u root password \"xxx\"\r\n\r\n#1. create databases and grant privileges\r\nmysql -p\r\nCREATE DATABASE exifweb CHARACTER SET utf8 COLLATE utf8_unicode_ci;\r\nCREATE DATABASE wordpress CHARACTER SET utf8 COLLATE utf8_unicode_ci;\r\nCREATE DATABASE owncloud702 CHARACTER SET utf8 COLLATE utf8_unicode_ci;\r\nCREATE DATABASE ghost CHARACTER SET utf8 COLLATE utf8_unicode_ci;\r\nCREATE DATABASE pydio CHARACTER SET utf8 COLLATE utf8_unicode_ci;\r\nGRANT ALL ON exifweb.* TO 'exifweb'@'%' IDENTIFIED BY 'exifweb' WITH GRANT OPTION;\r\nGRANT ALL ON wordpress.* TO 'wordpress'@'%' IDENTIFIED BY 'wordpress' WITH GRANT OPTION;\r\nGRANT ALL ON owncloud702.* TO 'owncloud702'@'%' IDENTIFIED BY 'owncloud702' WITH GRANT OPTION;\r\nGRANT ALL ON ghost.* TO 'ghost'@'%' IDENTIFIED BY 'ghost' WITH GRANT OPTION;\r\nGRANT ALL ON pydio.* TO 'pydio'@'%' IDENTIFIED BY 'pydio' WITH GRANT OPTION;\r\nFLUSH PRIVILEGES;\r\n\r\n#2. prepare sql for databases to restore\r\ncp \/i-data\/md0\/seagate-ext4\/ProjectsNew\/nsa310-config\/trunk\/mysql-db-design\/*.sql.gz $HOME\/temp\/mysql-restore\r\ncd $HOME\/temp\/mysql-restore\r\ngunzip exifweb.sql.gz\r\ngunzip wordpress.sql.gz\r\ngunzip owncloud702.sql.gz\r\ngunzip ghost.sql.gz\r\ngunzip pydio.sql.gz\r\nls -l *.sql\r\n\r\n#3. restore DBs\r\n# The config variable max_allowed_packet must be larger than the imported sql script!\r\n#see http:\/\/bugs.mysql.com\/bug.php?id=67179&files=1\r\n# Take five-tables.sql from me (it's a copy of the original):\r\nwget https:\/\/adrhc.go.ro\/public\/mysql-five-tables-5.6.25.sql\r\n# or take it from the original post:\r\n#wget http:\/\/bugs.mysql.com\/file.php?id=19725&bug_id=67179\r\nmv 'file.php?id=19725&bug_id=67179' mysql-five-tables-5.6.25.sql\r\n# this solves the innodb missing tables problem:\r\nmysql -p mysql < mysql-five-tables-5.6.25.sql\r\n# now import your databases:\r\nmysql -u root -p exifweb < nsa310-config-trunk\/mysql-db-design\/exifweb.sql\r\nmysql -u root -p wordpress < nsa310-config-trunk\/mysql-db-design\/wordpress.sql\r\nmysql -u root -p owncloud702 < nsa310-config-trunk\/mysql-db-design\/owncloud702.sql\r\nmysql -u root -p ghost < ghost.sql\r\nmysql -u root -p pydio < pydio.sql\r\nmysqlcheck -A --password=xxx --auto-repair\r\n\r\n#UPGRADE DB schema\r\n#If you use InnoDB, consider setting innodb_fast_shutdown to 0 before shutting down and upgrading your server.\r\n\/ffp\/start\/mysqld.sh stop\r\nsed -i s\/\"innodb_fast_shutdown\\s*=\\s*1\"\/\"innodb_fast_shutdown = 0\"\/ \/ffp\/etc\/my.cnf\r\ngrep innodb_fast_shutdown \/ffp\/etc\/my.cnf\r\n\/ffp\/start\/mysqld.sh start\r\n\/ffp\/start\/mysqld.sh stop\r\n\/ffp\/start\/mysqld.sh start\r\nmysql_upgrade -u root -p --socket=\/ffp\/var\/run\/mysql\/mysql.sock -v\r\n\/ffp\/start\/mysqld.sh stop\r\nsed -i s\/\"innodb_fast_shutdown\\s*=\\s*0\"\/\"innodb_fast_shutdown = 1\"\/ \/ffp\/etc\/my.cnf\r\ngrep innodb_fast_shutdown \/ffp\/etc\/my.cnf\r\n\/ffp\/start\/mysqld.sh start\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p># 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 [&hellip;]<\/p>\n<div class=\"link-more\"><a href=\"https:\/\/adrhc.go.ro\/blog\/create-mysql-system-db\/#more-1975\" class=\"more-link\">Continue reading &#10142; <span class=\"screen-reader-text\">Create mysql system db<\/span><\/a><\/div>","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9,12,8],"tags":[121],"class_list":["post-1975","post","type-post","status-publish","format-standard","hentry","category-commands","category-database","category-howto","tag-mysql"],"_links":{"self":[{"href":"https:\/\/adrhc.go.ro\/blog\/wp-json\/wp\/v2\/posts\/1975","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/adrhc.go.ro\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/adrhc.go.ro\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/adrhc.go.ro\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/adrhc.go.ro\/blog\/wp-json\/wp\/v2\/comments?post=1975"}],"version-history":[{"count":0,"href":"https:\/\/adrhc.go.ro\/blog\/wp-json\/wp\/v2\/posts\/1975\/revisions"}],"wp:attachment":[{"href":"https:\/\/adrhc.go.ro\/blog\/wp-json\/wp\/v2\/media?parent=1975"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/adrhc.go.ro\/blog\/wp-json\/wp\/v2\/categories?post=1975"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/adrhc.go.ro\/blog\/wp-json\/wp\/v2\/tags?post=1975"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}