mysql57 の履歴(No.7)

履歴一覧
差分を表示
現在との差分を表示
ソースを表示
mysql57 へ行く。
- 1 (2019-02-06 (水) 16:32:35)
- 2 (2019-02-06 (水) 16:34:39)
- 3 (2019-02-07 (木) 12:44:40)
- 4 (2019-02-08 (金) 13:45:13)
- 5 (2019-02-08 (金) 17:56:01)
- 6 (2019-02-10 (日) 08:31:56)
- 7 (2019-02-10 (日) 11:46:09)

CONTENTS

mysql57
- MySQLからパスワードを変更する
  - Safe mode でMySQL起動してパスワード変更
- Data_dir変更
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY

Lastmodified　2019-02-12 (火) 14:30:18

mysql57†

portinstall mysql57-server

スクラッチインストールで出るエラー

===>  openldap-sasl-client-2.4.47 conflicts with installed package(s):
      openldap-client-2.4.47

      They install files into the same place.
      You may want to stop build with Ctrl + C.
===>  License OPENLDAP accepted by the user
===>   openldap-sasl-client-2.4.47 depends on file: /usr/local/sbin/pkg - found
===> Fetching all distfiles required by openldap-sasl-client-2.4.47 for building

===>  openldap-sasl-client-2.4.47 conflicts with installed package(s):
      openldap-client-2.4.47

      They will not build together.
      Please remove them first with pkg delete.
*** Error code 1

Stop.
make[5]: stopped in /usr/ports/net/openldap24-sasl-client
*** Error code 1

# pkg info -r openldap-client-2.4.47
openldap-client-2.4.47:
        php72-ldap-7.2.14_1

# pkg delete openldap-client-2.4.47
Updating database digests format: 100%
Checking integrity... done (0 conflicting)
Deinstallation has been requested for the following 3 packages (of 0 packages in the universe):

Installed packages to be REMOVED:
        openldap-client-2.4.47
        php72-ldap-7.2.14_1
        php72-extensions-1.0

Number of packages to be removed: 3

The operation will free 6 MiB.

Proceed with deinstalling packages? [y/N]: y

本番環境だとこう Installed packages to be REMOVED:

       openldap-client-2.4.47
       php71-ldap-7.1.26_1
       php71-extensions-1.0

Installing mysql57-server-5.7.25...
===> Creating groups.
Creating group 'mysql' with gid '88'.
===> Creating users
Creating user 'mysql' with uid '88'.
*****************************************************************************

Remember to run mysql_upgrade the first time you start the MySQL server
after an upgrade from an earlier version.

Initial password for first time use of MySQL is saved in $HOME/.mysql_secret
ie. when you want to use "mysql -u root -p" first you should see password
in /root/.mysql_secret

MySQL57 has a default %%ETCDIR%%/my.cnf,
remember to replace it wit your own
or set `mysql_optfile="$YOUR_CNF_FILE` in rc.conf.

*****************************************************************************
===>  Cleaning for mysql57-client-5.7.25
===>  Cleaning for openldap-sasl-client-2.4.47
===>  Cleaning for cyrus-sasl-2.1.27
===>  Cleaning for mysql57-server-5.7.25

1011  11:14   portinstall php72-ldap
1012  11:15   portinstall php72-extensions

インストール直後のrootパスワードは、/root/.mysql_secret　にあるので、コレでログインし、パスワードを変更するには

↑

MySQLからパスワードを変更する†

SET PASSWORD FOR ユーザ名@ホスト名=password('新しいパスワード');

↑

Safe mode でMySQL起動してパスワード変更†

https://qiita.com/unbabel/items/b784459356686641dabe

$ mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

# /usr/local/etc/rc.d/mysql-server stop

$ mysqld_safe --skip-grant-tables --skip-networking

別のターミナルでMySQLを開く：

$ mysql
mysql> update user set authentication_string=password('PASSWORD') where user='root'；

https://qiita.com/garicchi/items/822ec2a074f09e0451c5

https://yoku0825.blogspot.com/2015/06/mysql-574defaultpasswordlifetime.html

mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
mysql_upgrade: Got error: 1862: Your password has expired. To log in you must change it using a client that supports expired passwords. while connecting to the MySQL server
Upgrade process encountered error and will not continue.

root@localhost [(none)]> alter user root password expire default;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
root@localhost [(none)]> SET PASSWORD FOR root@localhost=PASSWORD('hoge');
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost [(none)]> SET PASSWORD FOR root@localhost=PASSWORD('xxxxxxxx');
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost [(none)]> exit
Bye

↑

Data_dir変更†

/usr/ports/databases/mysql57-server/Makefile

MY_DBDIR=	/usr/home/mysql
MY_SECDIR=	/usr/home/mysql_secure
MY_TMPDIR=	/usr/home/mysql_tmpdir

と変えたいデータディレクトリで、コンパイルするのが確実か。

/usr/local/etc/mysql/my.cnf

#datadir                         = /var/db/mysql
datadir                         = /usr/home/mysql

#innodb_data_home_dir            = /var/db/mysql
#innodb_log_group_home_dir       = /var/db/mysql
innodb_data_home_dir            = /usr/home/mysql
innodb_log_group_home_dir       = /usr/home/mysql

↑

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY†

https://www.p-nt.com/technicblog/archives/204

my.cnfでsql_modeを変更します。

my.cnfの場所は、 mysql --help | grep my.cnf で表示されるので、以下の記述を追加します。

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

sql_modeで、ONLY_FULL_GROUP_BYを未指定にします。必要に応じてsql_modeの内容は変更してください。

MySQLを再起動してあげればエラーが表示されなくなります。

Total access 1235：本日 1：昨日 0

Counter: 1235, today: 1, yesterday: 0