MySQL on FreeBSD
をテンプレートにして作成
[
トップ
] [
新規
|
一覧
|
検索
|
最終更新
|
ヘルプ
|
ログイン
]
開始行:
CONTENTS
----
Lastmodified &lastmod;
#counter([total|today|yesterday]);
----
#contents
----
*mysql-80 root のパスワード変更 [#hdc73ea8]
root@localhost [mysql]> ALTER USER 'root'@'localhost' ID...
Query OK, 0 rows affected (0.00 sec)
*mysql_upgrade: unknown variable 'max_allowed_packet=16M'...
mysql_upgrade しようとしたところエラー
# mysql_upgrade -u root -p
mysql_upgrade: unknown variable 'max_allowed_packet=16M'
これは、my.cnf の[client]セクションに
max_allowed_packet = 16M
の記述があると現れるようで、[mysqld]セクションにのみ記述...
https://bugs.mysql.com/bug.php?id=68313
*MySQL が起動しない [#wbbe302d]
# /usr/local/etc/rc.d/mysql-server start ←デーモンを起動...
してもmysql daemon が起動しない・・・ ので、５５やら５４...
# rm -r /var/db/mysql または mv /var/db/mysql /var/db/m...
して、起動したらおｋ
設定ファイルも要チェックだよ
**mysqlフォルダをmvしても起動出来ない場合 [#v5b4d5ab]
エラーログ /var/db/mysql/FQDN.err をよ～く見る。
150829 11:25:21 InnoDB: Initializing buffer pool, size =...
150829 11:25:21 InnoDB: Completed initialization of buff...
150829 11:25:21 InnoDB: highest supported file format is...
150829 11:25:22 InnoDB: Waiting for the background thre...
150829 11:25:23 InnoDB: 5.5.44 started; log sequence num...
150829 11:25:23 [ERROR] /usr/local/libexec/mysqld: unkno...
150829 11:25:23 [ERROR] Aborting
150829 11:25:23 InnoDB: Starting shutdown...
150829 11:25:24 InnoDB: Shutdown completed; log sequenc...
150829 11:25:24 [Note] /usr/local/libexec/mysqld: Shutdo...
この場合は、/usr/local/etc/my.cnf に、
[mysqld]
socket = /tmp/mysql.sock
skip-locking この行があったための...
これで、起動できました。
**エラーログ /var/db/mysql/FQDN.err に「mysqld: File '....
2023-01-17T23:34:04.941808Z 1 [System] [MY-013576] [Inno...
2023-01-17T23:34:16.874925Z 1 [System] [MY-013577] [Inno...
mysqld: File './mysql-bin.002815' not found (OS errno 2 ...
2023-01-17T23:34:17.693982Z 0 [ERROR] [MY-010958] [Serve...
2023-01-17T23:34:17.694009Z 0 [ERROR] [MY-010041] [Serve...
2023-01-17T23:34:17.694022Z 0 [ERROR] [MY-010119] [Serve...
2023-01-17T23:34:18.880764Z 0 [System] [MY-010910] [Serv...
2023-01-17T23:41:40.575239Z 0 [Warning] [MY-010140] [Ser...
ログファイルにアクセス出来ないというエラーで、/var/db/mys...
ログファイルの情報は、/var/db/mysql/mysql-bin.index に記...
と、起動しました。
【参考URL】https://freefielder.jp/blog/2012/03/mysql-1.html
* 一つのDBを別サーバに移行 [#e8037454]
# mysqldump -a --user=UserName --password=PassWored DB_N...
# scp DB_Name.mysql TargetServer.example.domain:/var/db/
TargetServer.example.domain
phpMyAdminなどで、UserName DB_Name を作成しておくこと。...
root@k149:/var/db # mysql --user=UserName --password=Pas...
Warning: Using a password on the command line interface ...
ERROR 1045 (28000): Access denied for user 'UserName'@'l...
上手く行くと
Warning: Using a password on the command line interface ...
とか言われながらも、移行ＯＫ
*MySQL-Server 56 [#af582cc3]
Lastmodified &lastmod;
何かの依存関係で、自動的にMySQL-Client 56 が入ってしまっ...
# portinstall mysql56-server
Using existing group 'mysql'.
Using existing user 'mysql'.
********************************************************...
Remember to run mysql_upgrade the first time you start t...
after an upgrade from an earlier version.
********************************************************...
===> SECURITY REPORT:
This port has installed the following files which ...
servers and may therefore pose a remote security r...
/usr/local/libexec/mysqld
This port has installed the following startup scri...
these network services to be started at boot time.
/usr/local/etc/rc.d/mysql-server
If there are vulnerabilities in these programs the...
risk to the system. FreeBSD makes no guarantee abo...
ports included in the Ports Collection. Please typ...
to deinstall the port if this is a concern.
For more information, and contact details about th...
status of this software, see the following webpage:
http://www.mysql.com/
===> Cleaning for mysql56-server-5.6.24
***phpMyAdminの手当 [#z11c6f8d]
# mysql -u root -p
mysql> source /usr/local/www/phpMyAdmin/sql/create_table...
Query OK, 1 row affected (0.00 sec)
*ダウングレードしたらDBへのアクセス時のエラー回避ができな...
2013-03-25 13:47:36
/var/db/mysql/FQDN.err
1056 13:28 /usr/local/etc/rc.d/mysql-server stop
1057 13:29 cd /usr/ports/databases/
1058 13:29 ll
1059 13:30 cd /usr/ports/databases/mysql55-client
1060 13:30 make deinstall
1061 13:30 cd /usr/ports/databases/mysql55-server/
1062 13:30 make deinstall
1063 13:31 cd /usr/ports/databases/mysql56-c
1064 13:31 cd /usr/ports/databases/mysql56-client
1065 13:31 make install
1066 13:33 cd /usr/ports/databases/mysql56-server/
1067 13:33 make install
1068 13:42 rehash
1069 13:43 /usr/local/etc/rc.d/mysql-server start
1070 13:43 top
1071 13:43 /usr/local/etc/rc.d/mysql-server status
1072 13:44 history
root@g7:/usr/ports/databases/mysql56-server # mysql -V
mysql Ver 14.14 Distrib 5.6.10, for FreeBSD9.1 (amd64) ...
root@g7:/usr/ports/databases/mysql56-server #
**--sql-mode=value[,value[,value...]] [#qcbb697d]
http://search.net-newbie.com/mysql41/mysql-database-admin...
オプション値として、次の任意の組み合わせを設定できる。
REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE...
リセットするには、値を空白にする（--sql-mode=""）。
NO_AUTO_VALUE_ON_ZERO は、AUTO_INCREMENT カラムの処理に影...
NO_AUTO_VALUE_ON_ZERO を指定すると、0 のこの働きが抑制さ...
たとえば、mysqldump でテーブルをダンプしてから再読み込み...
この場合、ダンプしたファイルを再読み込みする前に NO_AUTO_...
----
*mysql 56 から 55へダウングレード [#yefa284e]
したら、起動しなくなりますた。
/var/db/mysql/FQDN.err
130323 11:04:58 mysqld_safe mysqld from pid file /home/m...
130323 11:05:15 mysqld_safe Starting mysqld daemon with ...
130323 11:05:15 InnoDB: The InnoDB memory heap is disabled
130323 11:05:15 InnoDB: Mutexes and rw_locks use GCC ato...
130323 11:05:15 InnoDB: Compressed tables use zlib 1.2.7
130323 11:05:15 InnoDB: Initializing buffer pool, size =...
130323 11:05:15 InnoDB: Completed initialization of buff...
InnoDB: Error: log file ./ib_logfile0 is of different si...
InnoDB: than specified in the .cnf file 0 5242880 bytes!
130323 11:05:15 [ERROR] Plugin 'InnoDB' init function re...
130323 11:05:15 [ERROR] Plugin 'InnoDB' registration as ...
130323 11:05:15 [ERROR] Unknown/unsupported storage engi...
130323 11:05:15 [ERROR] Aborting
130323 11:05:15 [Note] /usr/local/libexec/mysqld: Shutdo...
130323 11:05:15 mysqld_safe mysqld from pid file /home/m...
ib_logfile0 と ib_logfile1 を消去するも、起動コマンドで...
130323 11:38:54 [ERROR] Fatal error: Can't open and lock...
というエラーがでて、起動せず。
http://server-helper.doorblog.jp/archives/4988735.html
root@g7:/home/mysql # mysql_install_db
FATAL ERROR: Could not find ./bin/my_print_defaults
If you compiled from source, you need to run 'make insta...
copy the software into the correct location ready for op...
If you are using a binary release, you must either be at...
level of the extracted archive, or pass the --basedir op...
pointing to that location.
root@g7:/home/mysql # locate /bin/my_print_defaults
/usr/home/Backups/222_all/usr/local/bin/my_print_defaults
/usr/local/bin/my_print_defaults
root@g7:/home/mysql #
root@g7:/home/mysql #
root@g7:/home/mysql #
root@g7:/home/mysql #
root@g7:/home/mysql #
root@g7:/home/mysql # cd /usr/local
root@g7:/usr/local # mysql_install_db
Installing MySQL system tables...
130323 11:45:54 [ERROR] ./libexec/mysqld: Incorrect info...
ERROR: 1033 Incorrect information in file: './mysql/tab...
130323 11:45:54 [ERROR] Aborting
130323 11:45:54 [Note] ./libexec/mysqld: Shutdown complete
Installation of system tables failed! Examine the logs in
/home/mysql for more information.
You can try to start the mysqld daemon with:
shell> ./libexec/mysqld --skip-grant &
and use the command line tool ./bin/mysql
to connect to the mysql database and look at the grant t...
shell> ./bin/mysql -u root mysql
mysql> show tables
Try 'mysqld --help' if you have problems with paths. Us...
gives you a log in /home/mysql that may be helpful.
Please consult the MySQL manual section
'Problems running mysql_install_db', and the manual sect...
describes problems on your OS. Another information sour...
MySQL email archives available at http://lists.mysql.com/.
Please check all of the above before mailing us! And re...
you do mail us, you MUST use the ./bin/mysqlbug script!
root@g7:/usr/local #
root@g7:/home/mysql # chown -R mysql:mysql /home/mysql/m...
*mysql root パスワードの変更 2013/03/07 [#jf9d046a]
mysql> SET PASSWORD FOR root@localhost=PASSWORD('hoge');
Query OK, 0 rows affected (0.00 sec)
*g7へ導入 2012/12/20[#s57869d5]
g7# portinstall databases/mysql55-server
===> Installing rc.d startup script(s)
********************************************************...
Remember to run mysql_upgrade (with the optional --datad...
the first time you start the MySQL server after an upgra...
earlier version.
********************************************************...
install-info --quiet /usr/local/info/mysql.info /usr/loc...
===> Correct pkg-plist sequence to create group(s) and u...
===> Compressing manual pages for mysql-server-5.5.28
===> Registering installation for mysql-server-5.5.28
===> SECURITY REPORT:
This port has installed the following files which m...
servers and may therefore pose a remote security ri...
/usr/local/libexec/mysqld
This port has installed the following startup scrip...
these network services to be started at boot time.
/usr/local/etc/rc.d/mysql-server
If there are vulnerabilities in these programs ther...
risk to the system. FreeBSD makes no guarantee abou...
ports included in the Ports Collection. Please type...
to deinstall the port if this is a concern.
For more information, and contact details about the...
status of this software, see the following webpage:
http://www.mysql.com/
===> Cleaning for mysql-server-5.5.28
[Updating the pkgdb <format:bdb_btree> in /var/db/pkg .....
# cp /usr/local/share/mysql/my-large.cnf /usr/local/etc/...
# chmod 644 /usr/local/etc/my.cnf
デフォルトでは /var/db/mysql にデータベースファイルが作成...
mysql_dbdir="/path/to/mysql"
g7サーバでは、こんかいこれを
mysql_dbdir="/tank/mysql"
とした。
**管理用テーブル作成SQL [#r6246e79]
mysqlにログイン
mysql -u
管理用テーブル作成SQL実行
source /usr/local/www/phpMyAdmin/examples/create_tables....
mysql> source /usr/local/www/phpMyAdmin/examples/create_...
Query OK, 1 row affected (0.01 sec)
Database changed
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
mysql> grant all privileges on phpmyadmin.* to pma@local...
Query OK, 0 rows affected (0.00 sec)
管理用テーブル用ユーザ作成（パスワードは適宜変更）
grant all privileges on phpmyadmin.* to pma@localhost id...
設定ファイルをコピー
cp /usr/local/www/phpMyAdmin/config.sample.inc.php /usr/...
設定ファイルを編集
vi /usr/local/www/phpMyAdmin/config.inc.php
$cfg['Servers'][$i]['controlhost'] = ”;から
$cfg['Servers'][$i]['auth_swekey_config'] = ‘/etc/swekey...
コメントを解除
**http://FQDN/phpmyadmin でもアクセスするように [#j76df799]
/usr/local/www/apache22/data/phpmyadmin という空ディレク...
*server のmysql 51 から 55へ [#g0dd4518]
** MySQL51 で稼働中のサーバに55をインストールすると [#l92...
===> Registering installation for mysql55-client-5.5.4...
Installing mysql55-client-5.5.44_1...
pkg-static: mysql55-client-5.5.44_1 conflicts with mysql...
*** [fake-pkg] Error code 70
Stop in /usr/ports/databases/mysql55-client.
*** [install] Error code 1
となるため（あたりまえ？）
# pkg delete mysql51-client-5.1.73_3
Updating database digests format: 100%
Checking integrity... done (0 conflicting)
Deinstallation has been requested for the following 2 pa...
Installed packages to be REMOVED:
mysql51-client-5.1.73_3
mysql51-server-5.1.73_2
The operation will free 59 MiB.
Proceed with deinstalling packages? [y/N]: y
[1/2] Deinstalling mysql51-server-5.1.73_2...
==> You should manually remove the "mysql" user.
[1/2] Deleting files for mysql51-server-5.1.73_2: 100%
[2/2] Deinstalling mysql51-client-5.1.73_3...
[2/2] Deleting files for mysql51-client-5.1.73_3: 100%
してから、
# portinstall databases/mysql55-client
続いて
# portinstall databases/mysql55-server
ここで、バージョン確認。
# mysql -V
mysql Ver 14.14 Distrib 5.5.44, for FreeBSD9.3 (amd64) ...
ここで、そのままupgradeしようとすると
# mysql_upgrade -u root -p
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Error: Server version (5.1.73-log) does not match with t...
the server (5.5.44) with which this program was built/di...
use --skip-version-check to skip this check.
FATAL ERROR: Upgrade failed
で、サーバをリスタートしてみる
# /usr/local/etc/rc.d/mysql-server restart
Stopping mysql.
Waiting for PIDS: 742, 742.
Starting mysql.
起動せず。・・・、もう一回
# mysql_upgrade -u root -p
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
FATAL ERROR: Upgrade failed
と、ここで、MySQL_Data_Dir/FQDN.err をみると、
[ERROR] /usr/local/libexec/mysqld: unknown option '--ski...
エラーを参考にして、my.cnfを編集
skip-locking
↓
skip-external-locking
skip-federated
↓
#skip-federated
で、55があがるっぽい。
** ここで、mysql 51 でのdbを55へアップグレードする。 [#o...
# mysql_upgrade -u root -p
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=...
Running 'mysqlcheck' with connection arguments: '--port=...
BB3.phpbb_acl_groups OK
BB3.phpbb_acl_options OK
BB3.phpbb_acl_roles OK
BB3.phpbb_acl_roles_data OK
BB3.phpbb_acl_users OK
BB3.phpbb_attachments OK
BB3.phpbb_banlist OK
BB3.phpbb_bbcodes OK
BB3.phpbb_bookmarks OK
BB3.phpbb_bots OK
: :
: :
phpmyadmin.pma_column_info OK
phpmyadmin.pma_designer_coords OK
phpmyadmin.pma_history OK
phpmyadmin.pma_pdf_pages OK
phpmyadmin.pma_recent OK
phpmyadmin.pma_relation OK
phpmyadmin.pma_table_coords OK
phpmyadmin.pma_table_info OK
phpmyadmin.pma_table_uiprefs OK
phpmyadmin.pma_tracking OK
phpmyadmin.pma_userconfig OK
Running 'mysql_fix_privilege_tables'...
OK
#
できたっぽい( ´▽｀)ノ
**phpmyadminで確認 [#d1992bd0]
ということで、DBが55にUPされたので、最新版のphpMyAdmin-4....
&ref(phpmyadmin_mysql51to55.PNG);
** phpmyadmin のテーブルを作成する [#d44d928a]
phpMyAdminもアップされたので、追加されているテーブルを作...
mysql -u root -p < /usr/local/www/phpMyAdmin/sql/create_...
*MySQL 2012/07/17 [#c4e0c619]
MySQLの再構築ということで、portinstall databases/mysql51-...
portinstall databases/mysql55-server
-- Installing: /usr/local/man/man1/resolveip.1
-- Installing: /usr/local/man/man8/mysqld.8
===> Installing rc.d startup script(s)
********************************************************...
Remember to run mysql_upgrade (with the optional --datad...
the first time you start the MySQL server after an upgra...
earlier version.
********************************************************...
install-info --quiet /usr/local/info/mysql.info /usr/loc...
===> Correct pkg-plist sequence to create group(s) and u...
===> Compressing manual pages for mysql-server-5.5.25
===> Registering installation for mysql-server-5.5.25
===> SECURITY REPORT:
This port has installed the following files which ...
servers and may therefore pose a remote security r...
/usr/local/libexec/mysqld
This port has installed the following startup scri...
these network services to be started at boot time.
/usr/local/etc/rc.d/mysql-server
If there are vulnerabilities in these programs the...
risk to the system. FreeBSD makes no guarantee abo...
ports included in the Ports Collection. Please typ...
to deinstall the port if this is a concern.
For more information, and contact details about th...
status of this software, see the following webpage:
http://www.mysql.com/
===> Cleaning for mysql-server-5.5.25
piano2nd#
*OpnePNEで写真がUPできなくなった？ [#nc5bbbc6]
/usr/local/etc/my.conf で、
max_allowed_packet = 16M
としているにもかかわらず、
blackcube# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.1.60 FreeBSD port: mysql-server-5.1.60
Copyright (c) 2000, 2011, Oracle and/or its affiliates. ...
Oracle is a registered trademark of Oracle Corporation a...
affiliates. Other names may be trademarks of their respe...
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the cu...
mysql> show variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.38 sec)
mysql>
[client]
max_allowed_packet = 20M
mysql> show global variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)
mysql> SET GLOBAL max_allowed_packet=16*1024*1024;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.00 sec)
mysql>
/usr/local/etc/my.conf で、って、それ、/usr/local/etc/my....
そりゃファイル名違ってたらDBにスルーされるわなぁ (´д`)
http://d.hatena.ne.jp/sh2/20091002
*MySQL DB Backup [#hdeda97f]
**mysqldump [#bdf26654]
http://www2.nl.freebsd.org/mysql/doc/refman/5.1/ja/mysqld...
mysqldumpの最も一般的な用途は、データベース全体のバックア...
shell> mysqldump db_name > backup-file.sql
878 15:12 mysqldump -p RSB > RSB_bac.sql
ダンプファイルをサーバに戻し読みすることが可能です。
shell> mysql db_name < backup-file.sql
880 15:13 mysql -p RSB_bk < RSB_bac.sq
また、次のようにもできます。
shell> mysql -e "source /path-to-backup/backup-file.sql"...
mysqldumpは１つのMySQLサーバからデータをコピーすることで...
shell> mysqldump --opt db_name | mysql --host=remote_hos...
1つのコマンドで複数のデータベースをダンプすることが可能で...
shell> mysqldump --databases db_name1 [db_name2 ...] > m...
全てのデータベースをダンプするには、--all-databasesオプシ...
shell> mysqldump --all-databases > all_databases.sql
InnoDBテーブルに関して、mysqldumpはオンラインバックアップ...
shell> mysqldump --all-databases --single-transaction > ...
このバックアップはグローバルリードロックをダンプの最初に...
point-in-timeリカバリは、(もしくは「roll-forward」ーこれ...
shell> mysqldump --all-databases --master-data=2 > all_d...
または
shell> mysqldump --all-databases --flush-logs --master-d...
> all_databases.sql
--master-dataと--single-transactionオプションは同時に使用...
バックアップ作成の追加情報に関しては、項4.9.1. 「データベ...
ビューのバックアップの際問題が発生した場合、ビューに対す...
Top / Previous / Next / Up / Table of Contents
**mysqlhotcopy [#t8afeec6]
http://www2.nl.freebsd.org/mysql/doc/refman/5.1/ja/mysqlh...
mysqlhotcopyは元々Tim Bunceによって書かれ、提供されたPerl...
portinstall databases/mysql51-scripts
install -o root -g wheel -m 444 'msql2mysql.1' '/usr/lo...
install -o root -g wheel -m 444 'mysql_convert_table_fo...
install -o root -g wheel -m 444 'mysql_find_rows.1' '/u...
install -o root -g wheel -m 444 'mysql_fix_extensions.1...
install -o root -g wheel -m 444 'mysql_secure_installat...
install -o root -g wheel -m 444 'mysql_setpermission.1'...
install -o root -g wheel -m 444 'mysql_zap.1' '/usr/loc...
install -o root -g wheel -m 444 'mysqlaccess.1' '/usr/l...
install -o root -g wheel -m 444 'mysqldumpslow.1' '/usr...
install -o root -g wheel -m 444 'mysqlhotcopy.1' '/usr/...
install -o root -g wheel -m 444 'mysqld_multi.1' '/usr/...
===> Compressing manual pages for mysql-scripts-5.1.49
===> Registering installation for mysql-scripts-5.1.49
===> Cleaning for p5-DBD-mysql51-4.016
===> Cleaning for mysql-scripts-5.1.49
k222# rehash
k222# mysqlhotcopy
Database name to hotcopy not specified
/usr/local/bin/mysqlhotcopy Ver 1.23
Usage: /usr/local/bin/mysqlhotcopy db_name[./table_regex...
-?, --help display this help-screen and exit
-u, --user=# user for database login if not cur...
-p, --password=# password to use when connecting to...
in my.cnf, which is recommended)
-h, --host=# hostname for local server when con...
-P, --port=# port to use when connecting to loc...
-S, --socket=# socket to use when connecting to l...
--allowold don't abort if target dir already ...
--addtodest don't rename target dir if it exis...
--keepold don't delete previous (now renamed...
--noindices don't include full index files in ...
--method=# method for copy (only "cp" current...
-q, --quiet be silent except for errors
--debug enable debug
-n, --dryrun report actions without doing them
--regexp=# copy all databases with names matc...
--suffix=# suffix for names of copied databases
--checkpoint=# insert checkpoint entry into speci...
--flushlog flush logs once all tables are loc...
--resetmaster reset the binlog once all tables a...
--resetslave reset the master.info once all tab...
--tmpdir=# temporary directory (instead of /tmp)
--record_log_pos=# record slave and master status in ...
--chroot=# base directory of chroot jail in w...
Try 'perldoc /usr/local/bin/mysqlhotcopy' for more comp...
k222# mysqlhotcopy -p xxxxxxxxxx PNE3 PNE3_bk --allowold
Existing hotcopy directory renamed to '/var/db/mysql/PNE...
**AutoMySQLBackup [#v48849d9]
http://members.multimania.co.uk/wipe_out/automysqlbackup/
*MySQL port change [#w6d55793]
3306ポートへの不正アクセスが増加してきたので？
/usr/local/etc/my.conf
[client]
#password = your_password
#port = 3306
port = ****
socket = /tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
#port = 3306
port = ****
socket = /tmp/mysql.sock
これで、
piano:root {47} % netstat -an
Active Internet connections (including servers)
Proto Recv-Q Send-Q Local Address Foreign Addr...
tcp4 0 0 219.117.246.200.80 210.255.122.2...
tcp4 0 52 219.117.246.200.9997 210.255.122.2...
tcp4 0 0 219.117.246.200.9997 210.255.122.2...
tcp4 0 0 *.3306 *.* ...
が
tcp4 0 0 *.**** *.* ...
になる。
*MySQL Upgrade [#u0de8cfe]
**新規インストール [#d6bcfd72]
# portinstall databases/mysql51-client
# portinstall databases/mysql51-server
**再インストール [#s54364b7]
# portupgrade -f databases/mysql51-client
# portupgrade -f databases/mysql51-server
**バージョンアップ [#uea20e3f]
# portupgrade databases/mysql51-client
# portupgrade databases/mysql51-server
**以下の起動スクリプトで制御できます。 [#d4391efc]
# /usr/local/etc/rc.d/mysql-server start ←デーモンを起動...
# /usr/local/etc/rc.d/mysql-server restart ←デーモンを再...
# /usr/local/etc/rc.d/mysql-server stop ←デーモンを停止...
http://www.peach.ne.jp/freebsd/mysql.html
*MySQL on FreeBSD [#o72ad0b0]
http://www.peach.ne.jp/freebsd/mysql.html
http://www.bugbearr.jp/?FreeBSD%2FMySQL
http://uls.fam.cx/freebsd/archives/000093.html
http://www.machu.jp/diary/20060206.html#p01
**Port de install [#cecf05d3]
# portinstall databases/mysql51-client
# portinstall databases/mysql51-server
/etc/rc.conf
mysql_enable="YES"
デフォルトでは /var/db/mysql にデータベースファイルが作成...
mysql_dbdir="/path/to/mysql"
mysqlの設定ファイルの雛形をコピーしておきます。（より大規...
# cp /usr/local/share/mysql/my-medium.cnf /usr/local/etc...
# chmod 644 /usr/local/etc/my.cnf
以下の起動スクリプトで制御できます。
# /usr/local/etc/rc.d/mysql-server start ←デーモンを起動...
# /usr/local/etc/rc.d/mysql-server restart ←デーモンを再...
# /usr/local/etc/rc.d/mysql-server stop ←デーモンを停止...
起動できたら、rootユーザのパスワードを設定します。初期状...
# mysqladmin -u root -h localhost password 'XXXXXX' ←XXX...
**接続ユーザの作成・削除・パスワード変更 [#r64f8f0f]
これらは手動でやると慣れない人にはかなり大変なので、細か...
ここでは匿名ユーザなど root@localhost 以外を削除しておき...
mysql> DELETE FROM mysql.user WHERE user!='root' OR host...
mysql> FLUSH PRIVILEGES; ←権限テーブルの再読み込み
piano:root {86} % mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.28-rc-log FreeBSD port: mysql-server...
Type 'help;' or '\h' for help. Type '\c' to clear the bu...
mysql> create database TFC;
Query OK, 1 row affected (0.00 sec)
mysql> use TFC;
Database changed
mysql> grant all privileges on TFC.*
-> to tfc@localhost identified by 'tasaka';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
piano:root {89} % mysqlshow -u root -p
Enter password:
+--------------------+
| Databases |
+--------------------+
| information_schema |
| TFC |
| mysql |
| test |
+--------------------+
piano:root {90} %
piano:root {90} % mysql -u tfc -p TFC
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.28-rc-log FreeBSD port: mysql-server...
Type 'help;' or '\h' for help. Type '\c' to clear the bu...
mysql> create table TFC (
-> id int(11) auto_increment,
-> name varchar(255),
-> primary key (id)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_TFC |
+---------------+
| TFC |
+---------------+
1 row in set (0.00 sec)
mysql> describe TFC;
+-------+--------------+------+-----+---------+---------...
| Field | Type | Null | Key | Default | Extra ...
+-------+--------------+------+-----+---------+---------...
| id | int(11) | NO | PRI | NULL | auto_inc...
| name | varchar(255) | YES | | NULL | ...
+-------+--------------+------+-----+---------+---------...
2 rows in set (0.00 sec)
mysql>
終了行:
CONTENTS
----
Lastmodified &lastmod;
#counter([total|today|yesterday]);
----
#contents
----
*mysql-80 root のパスワード変更 [#hdc73ea8]
root@localhost [mysql]> ALTER USER 'root'@'localhost' ID...
Query OK, 0 rows affected (0.00 sec)
*mysql_upgrade: unknown variable 'max_allowed_packet=16M'...
mysql_upgrade しようとしたところエラー
# mysql_upgrade -u root -p
mysql_upgrade: unknown variable 'max_allowed_packet=16M'
これは、my.cnf の[client]セクションに
max_allowed_packet = 16M
の記述があると現れるようで、[mysqld]セクションにのみ記述...
https://bugs.mysql.com/bug.php?id=68313
*MySQL が起動しない [#wbbe302d]
# /usr/local/etc/rc.d/mysql-server start ←デーモンを起動...
してもmysql daemon が起動しない・・・ ので、５５やら５４...
# rm -r /var/db/mysql または mv /var/db/mysql /var/db/m...
して、起動したらおｋ
設定ファイルも要チェックだよ
**mysqlフォルダをmvしても起動出来ない場合 [#v5b4d5ab]
エラーログ /var/db/mysql/FQDN.err をよ～く見る。
150829 11:25:21 InnoDB: Initializing buffer pool, size =...
150829 11:25:21 InnoDB: Completed initialization of buff...
150829 11:25:21 InnoDB: highest supported file format is...
150829 11:25:22 InnoDB: Waiting for the background thre...
150829 11:25:23 InnoDB: 5.5.44 started; log sequence num...
150829 11:25:23 [ERROR] /usr/local/libexec/mysqld: unkno...
150829 11:25:23 [ERROR] Aborting
150829 11:25:23 InnoDB: Starting shutdown...
150829 11:25:24 InnoDB: Shutdown completed; log sequenc...
150829 11:25:24 [Note] /usr/local/libexec/mysqld: Shutdo...
この場合は、/usr/local/etc/my.cnf に、
[mysqld]
socket = /tmp/mysql.sock
skip-locking この行があったための...
これで、起動できました。
**エラーログ /var/db/mysql/FQDN.err に「mysqld: File '....
2023-01-17T23:34:04.941808Z 1 [System] [MY-013576] [Inno...
2023-01-17T23:34:16.874925Z 1 [System] [MY-013577] [Inno...
mysqld: File './mysql-bin.002815' not found (OS errno 2 ...
2023-01-17T23:34:17.693982Z 0 [ERROR] [MY-010958] [Serve...
2023-01-17T23:34:17.694009Z 0 [ERROR] [MY-010041] [Serve...
2023-01-17T23:34:17.694022Z 0 [ERROR] [MY-010119] [Serve...
2023-01-17T23:34:18.880764Z 0 [System] [MY-010910] [Serv...
2023-01-17T23:41:40.575239Z 0 [Warning] [MY-010140] [Ser...
ログファイルにアクセス出来ないというエラーで、/var/db/mys...
ログファイルの情報は、/var/db/mysql/mysql-bin.index に記...
と、起動しました。
【参考URL】https://freefielder.jp/blog/2012/03/mysql-1.html
* 一つのDBを別サーバに移行 [#e8037454]
# mysqldump -a --user=UserName --password=PassWored DB_N...
# scp DB_Name.mysql TargetServer.example.domain:/var/db/
TargetServer.example.domain
phpMyAdminなどで、UserName DB_Name を作成しておくこと。...
root@k149:/var/db # mysql --user=UserName --password=Pas...
Warning: Using a password on the command line interface ...
ERROR 1045 (28000): Access denied for user 'UserName'@'l...
上手く行くと
Warning: Using a password on the command line interface ...
とか言われながらも、移行ＯＫ
*MySQL-Server 56 [#af582cc3]
Lastmodified &lastmod;
何かの依存関係で、自動的にMySQL-Client 56 が入ってしまっ...
# portinstall mysql56-server
Using existing group 'mysql'.
Using existing user 'mysql'.
********************************************************...
Remember to run mysql_upgrade the first time you start t...
after an upgrade from an earlier version.
********************************************************...
===> SECURITY REPORT:
This port has installed the following files which ...
servers and may therefore pose a remote security r...
/usr/local/libexec/mysqld
This port has installed the following startup scri...
these network services to be started at boot time.
/usr/local/etc/rc.d/mysql-server
If there are vulnerabilities in these programs the...
risk to the system. FreeBSD makes no guarantee abo...
ports included in the Ports Collection. Please typ...
to deinstall the port if this is a concern.
For more information, and contact details about th...
status of this software, see the following webpage:
http://www.mysql.com/
===> Cleaning for mysql56-server-5.6.24
***phpMyAdminの手当 [#z11c6f8d]
# mysql -u root -p
mysql> source /usr/local/www/phpMyAdmin/sql/create_table...
Query OK, 1 row affected (0.00 sec)
*ダウングレードしたらDBへのアクセス時のエラー回避ができな...
2013-03-25 13:47:36
/var/db/mysql/FQDN.err
1056 13:28 /usr/local/etc/rc.d/mysql-server stop
1057 13:29 cd /usr/ports/databases/
1058 13:29 ll
1059 13:30 cd /usr/ports/databases/mysql55-client
1060 13:30 make deinstall
1061 13:30 cd /usr/ports/databases/mysql55-server/
1062 13:30 make deinstall
1063 13:31 cd /usr/ports/databases/mysql56-c
1064 13:31 cd /usr/ports/databases/mysql56-client
1065 13:31 make install
1066 13:33 cd /usr/ports/databases/mysql56-server/
1067 13:33 make install
1068 13:42 rehash
1069 13:43 /usr/local/etc/rc.d/mysql-server start
1070 13:43 top
1071 13:43 /usr/local/etc/rc.d/mysql-server status
1072 13:44 history
root@g7:/usr/ports/databases/mysql56-server # mysql -V
mysql Ver 14.14 Distrib 5.6.10, for FreeBSD9.1 (amd64) ...
root@g7:/usr/ports/databases/mysql56-server #
**--sql-mode=value[,value[,value...]] [#qcbb697d]
http://search.net-newbie.com/mysql41/mysql-database-admin...
オプション値として、次の任意の組み合わせを設定できる。
REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE...
リセットするには、値を空白にする（--sql-mode=""）。
NO_AUTO_VALUE_ON_ZERO は、AUTO_INCREMENT カラムの処理に影...
NO_AUTO_VALUE_ON_ZERO を指定すると、0 のこの働きが抑制さ...
たとえば、mysqldump でテーブルをダンプしてから再読み込み...
この場合、ダンプしたファイルを再読み込みする前に NO_AUTO_...
----
*mysql 56 から 55へダウングレード [#yefa284e]
したら、起動しなくなりますた。
/var/db/mysql/FQDN.err
130323 11:04:58 mysqld_safe mysqld from pid file /home/m...
130323 11:05:15 mysqld_safe Starting mysqld daemon with ...
130323 11:05:15 InnoDB: The InnoDB memory heap is disabled
130323 11:05:15 InnoDB: Mutexes and rw_locks use GCC ato...
130323 11:05:15 InnoDB: Compressed tables use zlib 1.2.7
130323 11:05:15 InnoDB: Initializing buffer pool, size =...
130323 11:05:15 InnoDB: Completed initialization of buff...
InnoDB: Error: log file ./ib_logfile0 is of different si...
InnoDB: than specified in the .cnf file 0 5242880 bytes!
130323 11:05:15 [ERROR] Plugin 'InnoDB' init function re...
130323 11:05:15 [ERROR] Plugin 'InnoDB' registration as ...
130323 11:05:15 [ERROR] Unknown/unsupported storage engi...
130323 11:05:15 [ERROR] Aborting
130323 11:05:15 [Note] /usr/local/libexec/mysqld: Shutdo...
130323 11:05:15 mysqld_safe mysqld from pid file /home/m...
ib_logfile0 と ib_logfile1 を消去するも、起動コマンドで...
130323 11:38:54 [ERROR] Fatal error: Can't open and lock...
というエラーがでて、起動せず。
http://server-helper.doorblog.jp/archives/4988735.html
root@g7:/home/mysql # mysql_install_db
FATAL ERROR: Could not find ./bin/my_print_defaults
If you compiled from source, you need to run 'make insta...
copy the software into the correct location ready for op...
If you are using a binary release, you must either be at...
level of the extracted archive, or pass the --basedir op...
pointing to that location.
root@g7:/home/mysql # locate /bin/my_print_defaults
/usr/home/Backups/222_all/usr/local/bin/my_print_defaults
/usr/local/bin/my_print_defaults
root@g7:/home/mysql #
root@g7:/home/mysql #
root@g7:/home/mysql #
root@g7:/home/mysql #
root@g7:/home/mysql #
root@g7:/home/mysql # cd /usr/local
root@g7:/usr/local # mysql_install_db
Installing MySQL system tables...
130323 11:45:54 [ERROR] ./libexec/mysqld: Incorrect info...
ERROR: 1033 Incorrect information in file: './mysql/tab...
130323 11:45:54 [ERROR] Aborting
130323 11:45:54 [Note] ./libexec/mysqld: Shutdown complete
Installation of system tables failed! Examine the logs in
/home/mysql for more information.
You can try to start the mysqld daemon with:
shell> ./libexec/mysqld --skip-grant &
and use the command line tool ./bin/mysql
to connect to the mysql database and look at the grant t...
shell> ./bin/mysql -u root mysql
mysql> show tables
Try 'mysqld --help' if you have problems with paths. Us...
gives you a log in /home/mysql that may be helpful.
Please consult the MySQL manual section
'Problems running mysql_install_db', and the manual sect...
describes problems on your OS. Another information sour...
MySQL email archives available at http://lists.mysql.com/.
Please check all of the above before mailing us! And re...
you do mail us, you MUST use the ./bin/mysqlbug script!
root@g7:/usr/local #
root@g7:/home/mysql # chown -R mysql:mysql /home/mysql/m...
*mysql root パスワードの変更 2013/03/07 [#jf9d046a]
mysql> SET PASSWORD FOR root@localhost=PASSWORD('hoge');
Query OK, 0 rows affected (0.00 sec)
*g7へ導入 2012/12/20[#s57869d5]
g7# portinstall databases/mysql55-server
===> Installing rc.d startup script(s)
********************************************************...
Remember to run mysql_upgrade (with the optional --datad...
the first time you start the MySQL server after an upgra...
earlier version.
********************************************************...
install-info --quiet /usr/local/info/mysql.info /usr/loc...
===> Correct pkg-plist sequence to create group(s) and u...
===> Compressing manual pages for mysql-server-5.5.28
===> Registering installation for mysql-server-5.5.28
===> SECURITY REPORT:
This port has installed the following files which m...
servers and may therefore pose a remote security ri...
/usr/local/libexec/mysqld
This port has installed the following startup scrip...
these network services to be started at boot time.
/usr/local/etc/rc.d/mysql-server
If there are vulnerabilities in these programs ther...
risk to the system. FreeBSD makes no guarantee abou...
ports included in the Ports Collection. Please type...
to deinstall the port if this is a concern.
For more information, and contact details about the...
status of this software, see the following webpage:
http://www.mysql.com/
===> Cleaning for mysql-server-5.5.28
[Updating the pkgdb <format:bdb_btree> in /var/db/pkg .....
# cp /usr/local/share/mysql/my-large.cnf /usr/local/etc/...
# chmod 644 /usr/local/etc/my.cnf
デフォルトでは /var/db/mysql にデータベースファイルが作成...
mysql_dbdir="/path/to/mysql"
g7サーバでは、こんかいこれを
mysql_dbdir="/tank/mysql"
とした。
**管理用テーブル作成SQL [#r6246e79]
mysqlにログイン
mysql -u
管理用テーブル作成SQL実行
source /usr/local/www/phpMyAdmin/examples/create_tables....
mysql> source /usr/local/www/phpMyAdmin/examples/create_...
Query OK, 1 row affected (0.01 sec)
Database changed
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
mysql> grant all privileges on phpmyadmin.* to pma@local...
Query OK, 0 rows affected (0.00 sec)
管理用テーブル用ユーザ作成（パスワードは適宜変更）
grant all privileges on phpmyadmin.* to pma@localhost id...
設定ファイルをコピー
cp /usr/local/www/phpMyAdmin/config.sample.inc.php /usr/...
設定ファイルを編集
vi /usr/local/www/phpMyAdmin/config.inc.php
$cfg['Servers'][$i]['controlhost'] = ”;から
$cfg['Servers'][$i]['auth_swekey_config'] = ‘/etc/swekey...
コメントを解除
**http://FQDN/phpmyadmin でもアクセスするように [#j76df799]
/usr/local/www/apache22/data/phpmyadmin という空ディレク...
*server のmysql 51 から 55へ [#g0dd4518]
** MySQL51 で稼働中のサーバに55をインストールすると [#l92...
===> Registering installation for mysql55-client-5.5.4...
Installing mysql55-client-5.5.44_1...
pkg-static: mysql55-client-5.5.44_1 conflicts with mysql...
*** [fake-pkg] Error code 70
Stop in /usr/ports/databases/mysql55-client.
*** [install] Error code 1
となるため（あたりまえ？）
# pkg delete mysql51-client-5.1.73_3
Updating database digests format: 100%
Checking integrity... done (0 conflicting)
Deinstallation has been requested for the following 2 pa...
Installed packages to be REMOVED:
mysql51-client-5.1.73_3
mysql51-server-5.1.73_2
The operation will free 59 MiB.
Proceed with deinstalling packages? [y/N]: y
[1/2] Deinstalling mysql51-server-5.1.73_2...
==> You should manually remove the "mysql" user.
[1/2] Deleting files for mysql51-server-5.1.73_2: 100%
[2/2] Deinstalling mysql51-client-5.1.73_3...
[2/2] Deleting files for mysql51-client-5.1.73_3: 100%
してから、
# portinstall databases/mysql55-client
続いて
# portinstall databases/mysql55-server
ここで、バージョン確認。
# mysql -V
mysql Ver 14.14 Distrib 5.5.44, for FreeBSD9.3 (amd64) ...
ここで、そのままupgradeしようとすると
# mysql_upgrade -u root -p
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Error: Server version (5.1.73-log) does not match with t...
the server (5.5.44) with which this program was built/di...
use --skip-version-check to skip this check.
FATAL ERROR: Upgrade failed
で、サーバをリスタートしてみる
# /usr/local/etc/rc.d/mysql-server restart
Stopping mysql.
Waiting for PIDS: 742, 742.
Starting mysql.
起動せず。・・・、もう一回
# mysql_upgrade -u root -p
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
FATAL ERROR: Upgrade failed
と、ここで、MySQL_Data_Dir/FQDN.err をみると、
[ERROR] /usr/local/libexec/mysqld: unknown option '--ski...
エラーを参考にして、my.cnfを編集
skip-locking
↓
skip-external-locking
skip-federated
↓
#skip-federated
で、55があがるっぽい。
** ここで、mysql 51 でのdbを55へアップグレードする。 [#o...
# mysql_upgrade -u root -p
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=...
Running 'mysqlcheck' with connection arguments: '--port=...
BB3.phpbb_acl_groups OK
BB3.phpbb_acl_options OK
BB3.phpbb_acl_roles OK
BB3.phpbb_acl_roles_data OK
BB3.phpbb_acl_users OK
BB3.phpbb_attachments OK
BB3.phpbb_banlist OK
BB3.phpbb_bbcodes OK
BB3.phpbb_bookmarks OK
BB3.phpbb_bots OK
: :
: :
phpmyadmin.pma_column_info OK
phpmyadmin.pma_designer_coords OK
phpmyadmin.pma_history OK
phpmyadmin.pma_pdf_pages OK
phpmyadmin.pma_recent OK
phpmyadmin.pma_relation OK
phpmyadmin.pma_table_coords OK
phpmyadmin.pma_table_info OK
phpmyadmin.pma_table_uiprefs OK
phpmyadmin.pma_tracking OK
phpmyadmin.pma_userconfig OK
Running 'mysql_fix_privilege_tables'...
OK
#
できたっぽい( ´▽｀)ノ
**phpmyadminで確認 [#d1992bd0]
ということで、DBが55にUPされたので、最新版のphpMyAdmin-4....
&ref(phpmyadmin_mysql51to55.PNG);
** phpmyadmin のテーブルを作成する [#d44d928a]
phpMyAdminもアップされたので、追加されているテーブルを作...
mysql -u root -p < /usr/local/www/phpMyAdmin/sql/create_...
*MySQL 2012/07/17 [#c4e0c619]
MySQLの再構築ということで、portinstall databases/mysql51-...
portinstall databases/mysql55-server
-- Installing: /usr/local/man/man1/resolveip.1
-- Installing: /usr/local/man/man8/mysqld.8
===> Installing rc.d startup script(s)
********************************************************...
Remember to run mysql_upgrade (with the optional --datad...
the first time you start the MySQL server after an upgra...
earlier version.
********************************************************...
install-info --quiet /usr/local/info/mysql.info /usr/loc...
===> Correct pkg-plist sequence to create group(s) and u...
===> Compressing manual pages for mysql-server-5.5.25
===> Registering installation for mysql-server-5.5.25
===> SECURITY REPORT:
This port has installed the following files which ...
servers and may therefore pose a remote security r...
/usr/local/libexec/mysqld
This port has installed the following startup scri...
these network services to be started at boot time.
/usr/local/etc/rc.d/mysql-server
If there are vulnerabilities in these programs the...
risk to the system. FreeBSD makes no guarantee abo...
ports included in the Ports Collection. Please typ...
to deinstall the port if this is a concern.
For more information, and contact details about th...
status of this software, see the following webpage:
http://www.mysql.com/
===> Cleaning for mysql-server-5.5.25
piano2nd#
*OpnePNEで写真がUPできなくなった？ [#nc5bbbc6]
/usr/local/etc/my.conf で、
max_allowed_packet = 16M
としているにもかかわらず、
blackcube# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.1.60 FreeBSD port: mysql-server-5.1.60
Copyright (c) 2000, 2011, Oracle and/or its affiliates. ...
Oracle is a registered trademark of Oracle Corporation a...
affiliates. Other names may be trademarks of their respe...
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the cu...
mysql> show variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.38 sec)
mysql>
[client]
max_allowed_packet = 20M
mysql> show global variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)
mysql> SET GLOBAL max_allowed_packet=16*1024*1024;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.00 sec)
mysql>
/usr/local/etc/my.conf で、って、それ、/usr/local/etc/my....
そりゃファイル名違ってたらDBにスルーされるわなぁ (´д`)
http://d.hatena.ne.jp/sh2/20091002
*MySQL DB Backup [#hdeda97f]
**mysqldump [#bdf26654]
http://www2.nl.freebsd.org/mysql/doc/refman/5.1/ja/mysqld...
mysqldumpの最も一般的な用途は、データベース全体のバックア...
shell> mysqldump db_name > backup-file.sql
878 15:12 mysqldump -p RSB > RSB_bac.sql
ダンプファイルをサーバに戻し読みすることが可能です。
shell> mysql db_name < backup-file.sql
880 15:13 mysql -p RSB_bk < RSB_bac.sq
また、次のようにもできます。
shell> mysql -e "source /path-to-backup/backup-file.sql"...
mysqldumpは１つのMySQLサーバからデータをコピーすることで...
shell> mysqldump --opt db_name | mysql --host=remote_hos...
1つのコマンドで複数のデータベースをダンプすることが可能で...
shell> mysqldump --databases db_name1 [db_name2 ...] > m...
全てのデータベースをダンプするには、--all-databasesオプシ...
shell> mysqldump --all-databases > all_databases.sql
InnoDBテーブルに関して、mysqldumpはオンラインバックアップ...
shell> mysqldump --all-databases --single-transaction > ...
このバックアップはグローバルリードロックをダンプの最初に...
point-in-timeリカバリは、(もしくは「roll-forward」ーこれ...
shell> mysqldump --all-databases --master-data=2 > all_d...
または
shell> mysqldump --all-databases --flush-logs --master-d...
> all_databases.sql
--master-dataと--single-transactionオプションは同時に使用...
バックアップ作成の追加情報に関しては、項4.9.1. 「データベ...
ビューのバックアップの際問題が発生した場合、ビューに対す...
Top / Previous / Next / Up / Table of Contents
**mysqlhotcopy [#t8afeec6]
http://www2.nl.freebsd.org/mysql/doc/refman/5.1/ja/mysqlh...
mysqlhotcopyは元々Tim Bunceによって書かれ、提供されたPerl...
portinstall databases/mysql51-scripts
install -o root -g wheel -m 444 'msql2mysql.1' '/usr/lo...
install -o root -g wheel -m 444 'mysql_convert_table_fo...
install -o root -g wheel -m 444 'mysql_find_rows.1' '/u...
install -o root -g wheel -m 444 'mysql_fix_extensions.1...
install -o root -g wheel -m 444 'mysql_secure_installat...
install -o root -g wheel -m 444 'mysql_setpermission.1'...
install -o root -g wheel -m 444 'mysql_zap.1' '/usr/loc...
install -o root -g wheel -m 444 'mysqlaccess.1' '/usr/l...
install -o root -g wheel -m 444 'mysqldumpslow.1' '/usr...
install -o root -g wheel -m 444 'mysqlhotcopy.1' '/usr/...
install -o root -g wheel -m 444 'mysqld_multi.1' '/usr/...
===> Compressing manual pages for mysql-scripts-5.1.49
===> Registering installation for mysql-scripts-5.1.49
===> Cleaning for p5-DBD-mysql51-4.016
===> Cleaning for mysql-scripts-5.1.49
k222# rehash
k222# mysqlhotcopy
Database name to hotcopy not specified
/usr/local/bin/mysqlhotcopy Ver 1.23
Usage: /usr/local/bin/mysqlhotcopy db_name[./table_regex...
-?, --help display this help-screen and exit
-u, --user=# user for database login if not cur...
-p, --password=# password to use when connecting to...
in my.cnf, which is recommended)
-h, --host=# hostname for local server when con...
-P, --port=# port to use when connecting to loc...
-S, --socket=# socket to use when connecting to l...
--allowold don't abort if target dir already ...
--addtodest don't rename target dir if it exis...
--keepold don't delete previous (now renamed...
--noindices don't include full index files in ...
--method=# method for copy (only "cp" current...
-q, --quiet be silent except for errors
--debug enable debug
-n, --dryrun report actions without doing them
--regexp=# copy all databases with names matc...
--suffix=# suffix for names of copied databases
--checkpoint=# insert checkpoint entry into speci...
--flushlog flush logs once all tables are loc...
--resetmaster reset the binlog once all tables a...
--resetslave reset the master.info once all tab...
--tmpdir=# temporary directory (instead of /tmp)
--record_log_pos=# record slave and master status in ...
--chroot=# base directory of chroot jail in w...
Try 'perldoc /usr/local/bin/mysqlhotcopy' for more comp...
k222# mysqlhotcopy -p xxxxxxxxxx PNE3 PNE3_bk --allowold
Existing hotcopy directory renamed to '/var/db/mysql/PNE...
**AutoMySQLBackup [#v48849d9]
http://members.multimania.co.uk/wipe_out/automysqlbackup/
*MySQL port change [#w6d55793]
3306ポートへの不正アクセスが増加してきたので？
/usr/local/etc/my.conf
[client]
#password = your_password
#port = 3306
port = ****
socket = /tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
#port = 3306
port = ****
socket = /tmp/mysql.sock
これで、
piano:root {47} % netstat -an
Active Internet connections (including servers)
Proto Recv-Q Send-Q Local Address Foreign Addr...
tcp4 0 0 219.117.246.200.80 210.255.122.2...
tcp4 0 52 219.117.246.200.9997 210.255.122.2...
tcp4 0 0 219.117.246.200.9997 210.255.122.2...
tcp4 0 0 *.3306 *.* ...
が
tcp4 0 0 *.**** *.* ...
になる。
*MySQL Upgrade [#u0de8cfe]
**新規インストール [#d6bcfd72]
# portinstall databases/mysql51-client
# portinstall databases/mysql51-server
**再インストール [#s54364b7]
# portupgrade -f databases/mysql51-client
# portupgrade -f databases/mysql51-server
**バージョンアップ [#uea20e3f]
# portupgrade databases/mysql51-client
# portupgrade databases/mysql51-server
**以下の起動スクリプトで制御できます。 [#d4391efc]
# /usr/local/etc/rc.d/mysql-server start ←デーモンを起動...
# /usr/local/etc/rc.d/mysql-server restart ←デーモンを再...
# /usr/local/etc/rc.d/mysql-server stop ←デーモンを停止...
http://www.peach.ne.jp/freebsd/mysql.html
*MySQL on FreeBSD [#o72ad0b0]
http://www.peach.ne.jp/freebsd/mysql.html
http://www.bugbearr.jp/?FreeBSD%2FMySQL
http://uls.fam.cx/freebsd/archives/000093.html
http://www.machu.jp/diary/20060206.html#p01
**Port de install [#cecf05d3]
# portinstall databases/mysql51-client
# portinstall databases/mysql51-server
/etc/rc.conf
mysql_enable="YES"
デフォルトでは /var/db/mysql にデータベースファイルが作成...
mysql_dbdir="/path/to/mysql"
mysqlの設定ファイルの雛形をコピーしておきます。（より大規...
# cp /usr/local/share/mysql/my-medium.cnf /usr/local/etc...
# chmod 644 /usr/local/etc/my.cnf
以下の起動スクリプトで制御できます。
# /usr/local/etc/rc.d/mysql-server start ←デーモンを起動...
# /usr/local/etc/rc.d/mysql-server restart ←デーモンを再...
# /usr/local/etc/rc.d/mysql-server stop ←デーモンを停止...
起動できたら、rootユーザのパスワードを設定します。初期状...
# mysqladmin -u root -h localhost password 'XXXXXX' ←XXX...
**接続ユーザの作成・削除・パスワード変更 [#r64f8f0f]
これらは手動でやると慣れない人にはかなり大変なので、細か...
ここでは匿名ユーザなど root@localhost 以外を削除しておき...
mysql> DELETE FROM mysql.user WHERE user!='root' OR host...
mysql> FLUSH PRIVILEGES; ←権限テーブルの再読み込み
piano:root {86} % mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.28-rc-log FreeBSD port: mysql-server...
Type 'help;' or '\h' for help. Type '\c' to clear the bu...
mysql> create database TFC;
Query OK, 1 row affected (0.00 sec)
mysql> use TFC;
Database changed
mysql> grant all privileges on TFC.*
-> to tfc@localhost identified by 'tasaka';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
piano:root {89} % mysqlshow -u root -p
Enter password:
+--------------------+
| Databases |
+--------------------+
| information_schema |
| TFC |
| mysql |
| test |
+--------------------+
piano:root {90} %
piano:root {90} % mysql -u tfc -p TFC
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.28-rc-log FreeBSD port: mysql-server...
Type 'help;' or '\h' for help. Type '\c' to clear the bu...
mysql> create table TFC (
-> id int(11) auto_increment,
-> name varchar(255),
-> primary key (id)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_TFC |
+---------------+
| TFC |
+---------------+
1 row in set (0.00 sec)
mysql> describe TFC;
+-------+--------------+------+-----+---------+---------...
| Field | Type | Null | Key | Default | Extra ...
+-------+--------------+------+-----+---------+---------...
| id | int(11) | NO | PRI | NULL | auto_inc...
| name | varchar(255) | YES | | NULL | ...
+-------+--------------+------+-----+---------+---------...
2 rows in set (0.00 sec)
mysql>
ページ名:
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
