Category: MySQL

Create a user For local mysql access

CREATE USER 'USERNAME-HERE'@'localhost' IDENTIFIED BY 'PASSWORD_HERE';
GRANT ALL PRIVILEGES ON DB_NAME_HERE.* TO 'USERNAME-HERE'@'localhost';
FLUSH PRIVILEGES;

CREATE USER 'USERNAME-HERE'@'localhost' IDENTIFIED BY 'PASSWORD_HERE';

GRANT ALL PRIVILEGES ON DB_NAME_HERE.* TO 'USERNAME-HERE'@'localhost';

FLUSH PRIVILEGES;

To allow remote access, run

CREATE USER 'USERNAME-HERE'@'%' IDENTIFIED BY 'PASSWORD_HERE';
GRANT ALL PRIVILEGES ON DB_NAME_HERE.* TO 'USERNAME-HERE'@'%';
FLUSH PRIVILEGES;

CREATE USER 'USERNAME-HERE'@'%' IDENTIFIED BY 'PASSWORD_HERE';

GRANT ALL PRIVILEGES ON DB_NAME_HERE.* TO 'USERNAME-HERE'@'%';

FLUSH PRIVILEGES;

To allow a user to create another user

GRANT GRANT OPTION ON *.* TO 'USERNAME-HERE'@'localhost';

1	GRANT GRANT OPTION ON . TO 'USERNAME-HERE'@'localhost';

Now this user will be able to create new users. Create a user with root privilages

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'MYSQL_PASSWORD' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'admin'@'localhost' WITH GRANT OPTION;

1 2	GRANT ALL PRIVILEGES ON . TO 'admin'@'localhost' IDENTIFIED BY 'MYSQL_PASSWORD' WITH GRANT OPTION; GRANT PROXY ON ''@'' TO 'admin'@'localhost' WITH GRANT OPTION;

For MySQL 8

CREATE USER 'admin'@'%' IDENTIFIED BY 'AEs308SuEtT0Hs';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;

1 2	CREATE USER 'admin'@'%' IDENTIFIED BY 'AEs308SuEtT0Hs'; GRANT ALL PRIVILEGES ON . TO 'admin'@'%' WITH GRANT OPTION;

[…]

On a server, starting MySQL i get following error

[root@3blogger log]# cat mysqld.log 
170102 21:34:02 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2017-01-02 21:34:02 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-01-02 21:34:02 0 [Note] /usr/sbin/mysqld (mysqld 5.6.24-72.2) starting as process 8048 ...
2017-01-02 21:34:02 8048 [Note] Plugin 'FEDERATED' is disabled.
2017-01-02 21:34:02 8048 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-01-02 21:34:02 8048 [Note] InnoDB: The InnoDB memory heap is disabled
2017-01-02 21:34:02 8048 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-01-02 21:34:02 8048 [Note] InnoDB: Memory barrier is not used
2017-01-02 21:34:02 8048 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-01-02 21:34:02 8048 [Note] InnoDB: Using Linux native AIO
2017-01-02 21:34:02 8048 [Note] InnoDB: Using CPU crc32 instructions
2017-01-02 21:34:02 8048 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2017-01-02 21:34:02 8048 [Note] InnoDB: Completed initialization of buffer pool
2017-01-02 21:34:02 8048 [Note] InnoDB: Highest supported file format is Barracuda.
2017-01-02 21:34:02 8048 [Note] InnoDB: Log scan progressed past the checkpoint lsn 644142666552
2017-01-02 21:34:02 8048 [Note] InnoDB: Database was not shutdown normally!
2017-01-02 21:34:02 8048 [Note] InnoDB: Starting crash recovery.
2017-01-02 21:34:02 8048 [Note] InnoDB: Reading tablespace information from the .ibd files...
2017-01-02 21:34:02 8048 [ERROR] InnoDB: space header page consists of zero bytes in tablespace ./3blogger/wp_wfNet404s.ibd (table 3blogger/wp_wfNet404s)
2017-01-02 21:34:02 8048 [Note] InnoDB: Page size:1024 Pages to analyze:64
2017-01-02 21:34:02 8048 [Note] InnoDB: Page size: 1024, Possible space_id count:0
2017-01-02 21:34:02 8048 [Note] InnoDB: Page size:2048 Pages to analyze:32
2017-01-02 21:34:02 8048 [Note] InnoDB: Page size: 2048, Possible space_id count:0
2017-01-02 21:34:02 8048 [Note] InnoDB: Page size:4096 Pages to analyze:16
2017-01-02 21:34:02 8048 [Note] InnoDB: Page size: 4096, Possible space_id count:0
2017-01-02 21:34:02 8048 [Note] InnoDB: Page size:8192 Pages to analyze:8
2017-01-02 21:34:02 8048 [Note] InnoDB: Page size: 8192, Possible space_id count:0
2017-01-02 21:34:02 8048 [Note] InnoDB: Page size:16384 Pages to analyze:4
2017-01-02 21:34:02 8048 [Note] InnoDB: Page size: 16384, Possible space_id count:0
2017-01-02 21:34:02 7fcef14617e0  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
InnoDB: Error: could not open single-table tablespace file ./3blogger/wp_wfNet404s.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.
170102 21:34:02 mysqld_safe mysqld from pid file /var/lib/mysql/3blogger.3blogger.com.pid ended
[root@3blogger log]#

[root@3blogger log]# cat mysqld.log

170102 21:34:02 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

2017-01-02 21:34:02 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2017-01-02 21:34:02 0 [Note] /usr/sbin/mysqld (mysqld 5.6.24-72.2) starting as process 8048 ...

2017-01-02 21:34:02 8048 [Note] Plugin 'FEDERATED' is disabled.

2017-01-02 21:34:02 8048 [Note] InnoDB: Using atomics to ref count buffer pool pages

2017-01-02 21:34:02 8048 [Note] InnoDB: The InnoDB memory heap is disabled

2017-01-02 21:34:02 8048 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2017-01-02 21:34:02 8048 [Note] InnoDB: Memory barrier is not used

2017-01-02 21:34:02 8048 [Note] InnoDB: Compressed tables use zlib 1.2.3

2017-01-02 21:34:02 8048 [Note] InnoDB: Using Linux native AIO

2017-01-02 21:34:02 8048 [Note] InnoDB: Using CPU crc32 instructions

2017-01-02 21:34:02 8048 [Note] InnoDB: Initializing buffer pool, size = 128.0M

2017-01-02 21:34:02 8048 [Note] InnoDB: Completed initialization of buffer pool

2017-01-02 21:34:02 8048 [Note] InnoDB: Highest supported file format is Barracuda.

2017-01-02 21:34:02 8048 [Note] InnoDB: Log scan progressed past the checkpoint lsn 644142666552

2017-01-02 21:34:02 8048 [Note] InnoDB: Database was not shutdown normally!

2017-01-02 21:34:02 8048 [Note] InnoDB: Starting crash recovery.

2017-01-02 21:34:02 8048 [Note] InnoDB: Reading tablespace information from the .ibd files...

2017-01-02 21:34:02 8048 [ERROR] InnoDB: space header page consists of zero bytes in tablespace ./3blogger/wp_wfNet404s.ibd (table 3blogger/wp_wfNet404s)

2017-01-02 21:34:02 8048 [Note] InnoDB: Page size:1024 Pages to analyze:64

2017-01-02 21:34:02 8048 [Note] InnoDB: Page size: 1024, Possible space_id count:0

2017-01-02 21:34:02 8048 [Note] InnoDB: Page size:2048 Pages to analyze:32

2017-01-02 21:34:02 8048 [Note] InnoDB: Page size: 2048, Possible space_id count:0

2017-01-02 21:34:02 8048 [Note] InnoDB: Page size:4096 Pages to analyze:16

2017-01-02 21:34:02 8048 [Note] InnoDB: Page size: 4096, Possible space_id count:0

2017-01-02 21:34:02 8048 [Note] InnoDB: Page size:8192 Pages to analyze:8

2017-01-02 21:34:02 8048 [Note] InnoDB: Page size: 8192, Possible space_id count:0

2017-01-02 21:34:02 8048 [Note] InnoDB: Page size:16384 Pages to analyze:4

2017-01-02 21:34:02 8048 [Note] InnoDB: Page size: 16384, Possible space_id count:0

2017-01-02 21:34:02 7fcef14617e0 InnoDB: Operating system error number 2 in a file operation.

InnoDB: The error means the system cannot find the path specified.

InnoDB: If you are installing InnoDB, remember that you must create

InnoDB: directories yourself, InnoDB does not create them.

InnoDB: Error: could not open single-table tablespace file ./3blogger/wp_wfNet404s.ibd

InnoDB: We do not continue the crash recovery, because the table may become

InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.

InnoDB: To fix the problem and start mysqld:

InnoDB: 1) If there is a permission problem in the file and mysqld cannot

InnoDB: open the file, you should modify the permissions.

InnoDB: 2) If the table is not needed, or you can restore it from a backup,

InnoDB: then you can remove the .ibd file, and InnoDB will do a normal

InnoDB: crash recovery and ignore that table.

InnoDB: 3) If the file system or the disk is broken, and you cannot remove

InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf

InnoDB: and force InnoDB to continue crash recovery here.

170102 21:34:02 mysqld_safe mysqld from pid file /var/lib/mysql/3blogger.3blogger.com.pid ended

[root@3blogger log]#

This was because the server used InnoDB database engine and my.cnf was not configured properly. First i removed the log files.

mkdir ~/mysql-problem-2017-01-03
mv /var/lib/mysql/ib_* ~/mysql-problem-2017-01-03

1 2	mkdir ~/mysql-problem-2017-01-03 mv /var/lib/mysql/ib_* ~/mysql-problem-2017-01-03

Now i edit /etc/my.cnf as follows

[root@3blogger etc]# cat /etc/my.cnf
[mysqld]

datadir=/var/lib/mysql
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
port        = 3306
socket=/var/lib/mysql/mysql.sock
back_log = 50
max_connections = 100
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default-storage-engine = MYISAM
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin=mysql-bin
binlog_format=mixed
slow_query_log
long_query_time = 2
server-id = 1
key_buffer_size = 32M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 8192
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[root@3blogger etc]#

[root@3blogger etc]# cat /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

port = 3306

socket=/var/lib/mysql/mysql.sock

back_log = 50

max_connections = 100

max_connect_errors = 10

table_open_cache = 2048

max_allowed_packet = 16M

binlog_cache_size = 1M

max_heap_table_size = 64M

read_buffer_size = 2M

read_rnd_buffer_size = 16M

sort_buffer_size = 8M

join_buffer_size = 8M

thread_cache_size = 8

thread_concurrency = 8

query_cache_size = 64M

query_cache_limit = 2M

ft_min_word_len = 4

default-storage-engine = MYISAM

thread_stack = 192K

transaction_isolation = REPEATABLE-READ

tmp_table_size = 64M

log-bin=mysql-bin

binlog_format=mixed

slow_query_log

long_query_time = 2

server-id = 1

key_buffer_size = 32M

bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

myisam_recover

innodb_additional_mem_pool_size = 16M

innodb_buffer_pool_size = 2G

innodb_data_file_path = ibdata1:10M:autoextend

innodb_file_io_threads = 4

innodb_thread_concurrency = 16

innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 8M

innodb_log_file_size = 256M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 512M

sort_buffer_size = 512M

read_buffer = 8M

write_buffer = 8M

[mysqlhotcopy]

interactive-timeout

[mysqld_safe]

open-files-limit = 8192

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[root@3blogger etc]#

This MySQL config is based on /usr/share/doc/mysql-server-5.1.73/my-innodb-heavy-4G.cnf provided my MySQL.

[root@server12 ~]# rpm -q --filesbypkg mysql-server-5.1.73-7.el6.x86_64 | grep .cnf
mysql-server              /usr/share/doc/mysql-server-5.1.73/my-huge.cnf
mysql-server              /usr/share/doc/mysql-server-5.1.73/my-innodb-heavy-4G.cnf
mysql-server              /usr/share/doc/mysql-server-5.1.73/my-large.cnf
mysql-server              /usr/share/doc/mysql-server-5.1.73/my-medium.cnf
mysql-server              /usr/share/doc/mysql-server-5.1.73/my-small.cnf
mysql-server              /usr/share/mysql/my-huge.cnf
mysql-server              /usr/share/mysql/my-innodb-heavy-4G.cnf
mysql-server              /usr/share/mysql/my-large.cnf
mysql-server              /usr/share/mysql/my-medium.cnf
mysql-server              /usr/share/mysql/my-small.cnf
[root@server12 ~]#

[root@server12 ~]# rpm -q --filesbypkg mysql-server-5.1.73-7.el6.x86_64 | grep .cnf

Reset MySQL root Password

MySQL User

Debian Allow MySQL root user to login using phpMyAdmin

Configuring MySQL .my.cnf

InnoDB: space header page consists of zero bytes in tablespace

myisamchk: Argument list too long