Not the answer you need?
Register and ask your own question!

after change config - Can't open and lock privilege tables

umarzukiumarzuki ContributorCurrent User Role Beginner
ubuntu 14.04 amd64
percona 5.6

After changing my config, I decided to restart second node and found permission errors. It seems that all files under data dir has become owned by root:root. What gives?
WSREP_SST: [INFO] Waiting for SST streaming to complete! (20150520 13:23:58.751)
2015-05-20 13:23:59 15145 [Note] WSREP: (6928fbc1, 'tcp://0.0.0.0:4567') turning message relay requesting off
2015-05-20 13:25:52 15145 [Note] WSREP: 0.0 (db1): State transfer to 1.0 (db2) complete.
2015-05-20 13:25:52 15145 [Note] WSREP: Member 0.0 (db1) synced with group.
WSREP_SST: [INFO] Preparing the backup at /var/lib/mysql//.sst (20150520 13:25:52.171)
WSREP_SST: [INFO] Evaluating innobackupex --no-version-check  --apply-log $rebuildcmd ${DATA} &>${DATA}/innobackup.prepare.log (20150520 13:25:52.175)
rm: cannot remove ‘/var/lib/mysql//innobackup.prepare.log’: No such file or directory
rm: cannot remove ‘/var/lib/mysql//innobackup.move.log’: No such file or directory
WSREP_SST: [INFO] Moving the backup to /var/lib/mysql/ (20150520 13:26:01.720)
WSREP_SST: [INFO] Evaluating innobackupex --defaults-file=/etc/mysql/my.cnf --no-version-check   --move-back --force-non-empty-directories ${DATA} &>${DATA}/innobackup.move.log (20150520 13:26:01.724)
WSREP_SST: [INFO] Move successful, removing /var/lib/mysql//.sst (20150520 13:26:02.770)
WSREP_SST: [INFO] Galera co-ords from recovery: 19bf9b09-ed47-11e4-8005-f75eee435bfc:930465 (20150520 13:26:02.788)
WSREP_SST: [INFO] Total time on joiner: 0 seconds (20150520 13:26:02.793)
WSREP_SST: [INFO] Removing the sst_in_progress file (20150520 13:26:02.798)
2015-05-20 13:26:02 15145 [Note] WSREP: SST complete, seqno: 930465
2015-05-20 13:26:02 15145 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-05-20 13:26:02 15145 [Note] InnoDB: The InnoDB memory heap is disabled
2015-05-20 13:26:02 15145 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-05-20 13:26:02 15145 [Note] InnoDB: Memory barrier is not used
2015-05-20 13:26:02 15145 [Note] InnoDB: Compressed tables use zlib 1.2.8
2015-05-20 13:26:02 15145 [Note] InnoDB: Using Linux native AIO
2015-05-20 13:26:02 15145 [Note] InnoDB: Using CPU crc32 instructions
2015-05-20 13:26:02 15145 [Note] InnoDB: Initializing buffer pool, size = 91.9G
2015-05-20 13:26:07 15145 [Note] InnoDB: Completed initialization of buffer pool
2015-05-20 13:26:08 15145 [Note] InnoDB: Highest supported file format is Barracuda.
2015-05-20 13:26:10 15145 [Note] InnoDB: 128 rollback segment(s) are active.
2015-05-20 13:26:10 15145 [Note] InnoDB: Waiting for purge to start
2015-05-20 13:26:10 15145 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.22-72.0 started; log sequence number 8113619938
2015-05-20 13:26:42 15145 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: cc1d0d52-feb0-11e4-8fda-40f2e99e3ca2.
2015-05-20 13:26:42 15145 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2015-05-20 13:26:42 15145 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2015-05-20 13:26:42 15145 [Note] Server hostname (bind-address): '10.1.1.100'; port: 3306
2015-05-20 13:26:42 15145 [Note]   - '10.1.1.100' resolves to '10.1.1.100';
2015-05-20 13:26:42 15145 [Note] Server socket created on IP: '10.1.1.100'.
2015-05-20 13:26:42 15145 [ERROR] /usr/sbin/mysqld: Can't find file: './mysql/user.frm' (errno: 13 - Permission denied)
2015-05-20 13:26:42 15145 [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: './mysql/user.frm' (errno: 13 - Permission denied)
150520 13:26:42 mysqld_safe mysqld from pid file /var/lib/mysql/mysqld.pid ended

my.cnf
[client]
port        = 3306
socket        = /var/run/mysqld/mysqld.sock
[mysqld_safe]
pid_file=mysqld.pid
log_error=/var/log/mysql.log
basedir=/usr/
datadir=/var/lib/mysql
[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket        = /var/run/mysqld/mysqld.sock
port        = 3306
basedir        = /usr
datadir        = /var/lib/mysql
tmpdir        = /tmp
lc-messages-dir    = /usr/share/mysql
federated
skip-external-locking
bind-address        = 10.1.1.100
key_buffer        = 24M
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet    = 512M
skip_name_resolve
memlock
sysdate_is_now=1
#thread_stack        = 192K
thread_cache_size       = 512
query_cache_size = 0
table_open_cache=1024
lower_case_table_names=0
# 5.6 backwards compatibility
explicit_defaults_for_timestamp=1
#myisam-recover         = BACKUP
#query_cache_limit    = 1M
#query_cache_size        = 16M
log_error = /var/log/mysql.log
binlog_format=ROW
### INNODB OPTIONS
innodb_buffer_pool_size=94065M
innodb_flush_log_at_trx_commit=2
innodb_file_per_table=1
innodb_data_file_path = ibdata1:100M:autoextend
## You may want to tune the below depending on number of cores and disk sub
innodb_read_io_threads=4
innodb_write_io_threads=4
innodb_io_capacity=200
innodb_doublewrite=1
innodb_log_file_size=1024M
innodb_log_buffer_size=96M
innodb_buffer_pool_instances=8
innodb_log_files_in_group=2
innodb_thread_concurrency=64
#innodb_file_format=barracuda
innodb_flush_method = O_DIRECT
innodb_autoinc_lock_mode=2
## avoid statistics update when doing e.g show tables
innodb_stats_on_metadata=0
default_storage_engine=innodb
wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_cluster_name = dbcluster
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=root:password
wsrep_node_name = db2
#wsrep_cluster_address=gcomm://
wsrep_cluster_address=gcomm://10.1.1.100:4567,10.1.1.100:4567
expire_logs_days    = 10
max_binlog_size         = 100M
# replicate myisam
#wsrep_replicate_myisam=1
[mysqldump]
quick
quote-names
max_allowed_packet    = 16M
[mysql]
[isamchk]
key_buffer        = 16M
!includedir /etc/mysql/conf.d/

Comments

  • umarzukiumarzuki Contributor Current User Role Beginner
    I changed back my setting of my.cnf to as below (cut and paste content, not using cp)
    [client]
    port        = 3306
    socket        = /var/run/mysqld/mysqld.sock
    [mysqld_safe]
    socket        = /var/run/mysqld/mysqld.sock
    nice        = 0
    [mysqld]
    user        = mysql
    pid-file    = /var/run/mysqld/mysqld.pid
    socket        = /var/run/mysqld/mysqld.sock
    port        = 3306
    basedir        = /usr
    datadir        = /var/lib/mysql
    tmpdir        = /tmp
    lc-messages-dir    = /usr/share/mysql
    federated
    skip-external-locking
    bind-address        = 10.1.1.100
    key_buffer              = 24M
    tmp_table_size = 64M
    max_heap_table_size = 64M
    max_allowed_packet      = 512M
    skip_name_resolve
    memlock
    sysdate_is_now=1
    thread_cache_size       = 512
    query_cache_size = 0
    table_open_cache=1024
    lower_case_table_names=0
    explicit_defaults_for_timestamp=1
    log_error = /var/log/mysql.log
    binlog_format=ROW
    innodb_buffer_pool_size=94065M
    innodb_flush_log_at_trx_commit=2
    innodb_file_per_table=1
    innodb_data_file_path = ibdata1:100M:autoextend
    innodb_read_io_threads=4
    innodb_write_io_threads=4
    innodb_io_capacity=200
    innodb_doublewrite=1
    innodb_log_file_size=1024M
    innodb_log_buffer_size=96M
    innodb_buffer_pool_instances=8
    innodb_log_files_in_group=2
    innodb_thread_concurrency=64
    innodb_flush_method = O_DIRECT
    innodb_autoinc_lock_mode=2
    innodb_stats_on_metadata=0
    default_storage_engine=innodb
    wsrep_provider=/usr/lib/libgalera_smm.so
    wsrep_cluster_name = dbcluster
    wsrep_sst_method=rsync
    wsrep_node_name = db2
    wsrep_cluster_address=gcomm://10.1.1.100:4567,10.1.1.100:4567,10.1.2.171:4567
    expire_logs_days    = 10
    max_binlog_size         = 100M
    wsrep_replicate_myisam=1
    [mysqldump]
    quick
    quote-names
    max_allowed_packet    = 16M
    [mysql]
    [isamchk]
    key_buffer        = 16M
    !includedir /etc/mysql/conf.d/
    

    and it works fine so far.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.