Category: MySQL – Page 3

MySQL root password in VestaCP

Posted on March 23, 2020 by ServerOk

In VestaCP server, MySQL root password is stored in file /usr/local/vesta/conf/mysql.conf, to find MySQL root password, run

cat /usr/local/vesta/conf/mysql.conf

1	cat /usr/local/vesta/conf/mysql.conf

[…]

MySQL 8 use caching_sha2_password as default autentication method. Many mysql clients still not support this method. If you need to use old method, you can set autentication plugin as mysql_native_password. You can also set this as default method by editing my.cnf file.

[mysqld]
default_authentication_plugin=mysql_native_password

1 2	[mysqld] default_authentication_plugin=mysql_native_password

To change a user to use mysql_native_password, run

ALTER USER ‘username’@’localhost’ IDENTIFIED WITH ‘mysql_native_password’ BY ‘PASSWORD_HERE’;

1	ALTER USER ‘username’@’localhost’ IDENTIFIED WITH ‘mysql_native_password’ BY ‘PASSWORD_HERE’;

[…]

Disable MySQL bin log on Bitnami

Posted on February 26, 2020 by ServerOk

if you have cloud sevrer with less disk space, it is better disable MySQL bin log as it take approx 3 GB of disk space on bitnami server. To disable MySQL binlog, edit mysql config file.

vi /opt/bitnami/mysql/my.cnf

1	vi /opt/bitnami/mysql/my.cnf

Find

[mysqld]

[mysqld]

Add below

disable_log_bin

1	disable_log_bin

Here is what i have in the my.cnf

root@wordpress-vm:~# cat /opt/bitnami/mysql/my.cnf

[mysqladmin]
user=root

[mysqld]
disable_log_bin
basedir=/opt/bitnami/mysql
datadir="/opt/bitnami/mysql/data"
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
tmpdir=/opt/bitnami/mysql/tmp
max_allowed_packet=32M
bind-address=127.0.0.1
skip-name-resolve=1
default-authentication-plugin=mysql_native_password

character-set-server=UTF8
collation-server=utf8_general_ci
[client]
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock

default-character-set=UTF8
[manager]
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
pid-file=/opt/bitnami/mysql/tmp/manager.pid
default-mysqld-path=/opt/bitnami/mysql/bin/mysqld.bin


!include /opt/bitnami/mysql/bitnami/my.cnf
root@wordpress-vm:~#

root@wordpress-vm:~# cat /opt/bitnami/mysql/my.cnf

[mysqladmin]

user=root

[mysqld]

disable_log_bin

basedir=/opt/bitnami/mysql

datadir="/opt/bitnami/mysql/data"

port=3306

socket=/opt/bitnami/mysql/tmp/mysql.sock

tmpdir=/opt/bitnami/mysql/tmp

max_allowed_packet=32M

bind-address=127.0.0.1

skip-name-resolve=1

default-authentication-plugin=mysql_native_password

character-set-server=UTF8

collation-server=utf8_general_ci

[client]

port=3306

socket=/opt/bitnami/mysql/tmp/mysql.sock

default-character-set=UTF8

[manager]

port=3306

socket=/opt/bitnami/mysql/tmp/mysql.sock

pid-file=/opt/bitnami/mysql/tmp/manager.pid

default-mysqld-path=/opt/bitnami/mysql/bin/mysqld.bin

!include /opt/bitnami/mysql/bitnami/my.cnf

root@wordpress-vm:~#

Now restart MySQL

/opt/bitnami/ctlscript.sh stop mysql
/opt/bitnami/ctlscript.sh start mysql

1 2	/opt/bitnami/ctlscript.sh stop mysql /opt/bitnami/ctlscript.sh start mysql

[…]

MySQL server has gone away

Posted on February 5, 2020 by ServerOk

When restoring a MySQL database, i get error “MySQL server has gone away”.

# mysql -u root -p'serverok123' sok_wp < backup.sql
ERROR 2006 (HY000) at line 5095: MySQL server has gone away
#

# mysql -u root -p'serverok123' sok_wp < backup.sql

ERROR 2006 (HY000) at line 5095: MySQL server has gone away

To fix this, edit MySQL configuration file. Add following line under [mysqld] section.

max_allowed_packet = 256M

1	max_allowed_packet = 256M

Restart MySQL

systemctl restart mysql

1	systemctl restart mysql

If you use MariaDB

systemctl restart mariadb

1	systemctl restart mariadb

Aria engine is not enabled or did not start

Posted on January 19, 2020 by ServerOk

When starting MaraDB, i got error

root@localhost:/var/log/mysql# cat error.log
2020-01-19  6:18:46 0 [ERROR] mysqld: Can't lock aria control file '/var/lib/mysql/aria_log_control' for exclusive use, error: 11. Will retry for 30 seconds
2020-01-19  6:19:17 0 [ERROR] mysqld: Got error 'Could not get an exclusive lock; file is probably in use by another process' when trying to use aria control file '/var/lib/mysql/aria_log_control'
2020-01-19  6:19:17 0 [ERROR] Plugin 'Aria' init function returned error.
2020-01-19  6:19:17 0 [ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.
2020-01-19  6:19:17 0 [Note] InnoDB: Using Linux native AIO
2020-01-19  6:19:17 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-01-19  6:19:17 0 [Note] InnoDB: Uses event mutexes
2020-01-19  6:19:17 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-01-19  6:19:17 0 [Note] InnoDB: Number of pools: 1
2020-01-19  6:19:17 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-01-19  6:19:17 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-01-19  6:19:17 0 [Note] InnoDB: Completed initialization of buffer pool
2020-01-19  6:19:17 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-01-19  6:19:17 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=2137316301
2020-01-19  6:19:17 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-01-19  6:19:17 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2020-01-19  6:19:17 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-01-19  6:19:17 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-01-19  6:19:17 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-01-19  6:19:17 0 [Note] InnoDB: Waiting for purge to start
2020-01-19  6:19:17 0 [Note] InnoDB: 10.3.18 started; log sequence number 2137316310; transaction id 2275296
2020-01-19  6:19:17 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-01-19  6:19:17 0 [ERROR] Aria engine is not enabled or did not start. The Aria engine must be enabled to continue as mysqld was configured with --with-aria-tmp-tables
2020-01-19  6:19:17 0 [ERROR] Aborting

root@localhost:/var/log/mysql#

root@localhost:/var/log/mysql# cat error.log

2020-01-19 6:18:46 0 [ERROR] mysqld: Can't lock aria control file '/var/lib/mysql/aria_log_control' for exclusive use, error: 11. Will retry for 30 seconds

2020-01-19 6:19:17 0 [ERROR] mysqld: Got error 'Could not get an exclusive lock; file is probably in use by another process' when trying to use aria control file '/var/lib/mysql/aria_log_control'

2020-01-19 6:19:17 0 [ERROR] Plugin 'Aria' init function returned error.

2020-01-19 6:19:17 0 [ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.

2020-01-19 6:19:17 0 [Note] InnoDB: Using Linux native AIO

2020-01-19 6:19:17 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2020-01-19 6:19:17 0 [Note] InnoDB: Uses event mutexes

2020-01-19 6:19:17 0 [Note] InnoDB: Compressed tables use zlib 1.2.11

2020-01-19 6:19:17 0 [Note] InnoDB: Number of pools: 1

2020-01-19 6:19:17 0 [Note] InnoDB: Using SSE2 crc32 instructions

2020-01-19 6:19:17 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M

2020-01-19 6:19:17 0 [Note] InnoDB: Completed initialization of buffer pool

2020-01-19 6:19:17 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().

2020-01-19 6:19:17 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=2137316301

2020-01-19 6:19:17 0 [Note] InnoDB: 128 out of 128 rollback segments are active.

2020-01-19 6:19:17 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"

2020-01-19 6:19:17 0 [Note] InnoDB: Creating shared tablespace for temporary tables

2020-01-19 6:19:17 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

2020-01-19 6:19:17 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.

2020-01-19 6:19:17 0 [Note] InnoDB: Waiting for purge to start

2020-01-19 6:19:17 0 [Note] InnoDB: 10.3.18 started; log sequence number 2137316310; transaction id 2275296

2020-01-19 6:19:17 0 [Note] Plugin 'FEEDBACK' is disabled.

2020-01-19 6:19:17 0 [ERROR] Aria engine is not enabled or did not start. The Aria engine must be enabled to continue as mysqld was configured with --with-aria-tmp-tables

2020-01-19 6:19:17 0 [ERROR] Aborting

root@localhost:/var/log/mysql#

To fix error, remove the file

mv /var/lib/mysql/aria_log_control /root/

1	mv /var/lib/mysql/aria_log_control /root/

[…]

Install MariaDB 10.3 on CentOS 7

Posted on November 22, 2019 by ServerOk

MariaDB is an Open Source MySQL drop in replacement. MariaDB provides repository for various operating systems at http://downloads.mariadb.org/mariadb/repositories/ To install MariaDB 10.3 on CentOS 7, create file

vi /etc/yum.repos.d/MariaDB.repo

1	vi /etc/yum.repos.d/MariaDB.repo

Add

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

[mariadb]

name = MariaDB

baseurl = http://yum.mariadb.org/10.3/centos7-amd64

gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck=1

Install MariaDB server and client with

yum install MariaDB-server MariaDB-client

1	yum install MariaDB-server MariaDB-client

Enable MariaDB start on boot

systemctl enable mariadb

1	systemctl enable mariadb

Start MariaDB

systemctl start mariadb

1	systemctl start mariadb

[…]

MySQL ERROR Access denied you need SUPER privilege for this operation

Posted on November 5, 2019 (November 5, 2019) by ServerOk

WHen restoring a MySQL database, i get following error

root@ns3043014:~# mysql -u NEW_DB_USER -p'PASSOWRD_HERE' DB_NAME < BACKUP_FILE.sql
ERROR 1227 (42000) at line 4382: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
root@ns3043014:~#

root@ns3043014:~# mysql -u NEW_DB_USER -p'PASSOWRD_HERE' DB_NAME < BACKUP_FILE.sql

ERROR 1227 (42000) at line 4382: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

root@ns3043014:~#

I opened the file in vim editor. On checking line 4382, i found following

/*!50013 DEFINER=`OLD_DB_USER`@`localhost` SQL SECURITY DEFINER */

1	/!50013 DEFINER=`OLD_DB_USER`@`localhost` SQL SECURITY DEFINER /

This is because some stored procedures use old database user as definer. To fix you need to find and replace all entry for old username with new username. In vim, […]

MySQL Recovering after a crash using tc.log

Posted on September 23, 2019 by ServerOk

After MySQL upgrade from MraiaDB 10.1 to 10.3, MySQL failed to start. On checking log, found

root@localhost:/var/log/mysql# cat error.log
2019-09-23  6:30:10 0 [Note] InnoDB: Using Linux native AIO
2019-09-23  6:30:10 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-09-23  6:30:10 0 [Note] InnoDB: Uses event mutexes
2019-09-23  6:30:10 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-09-23  6:30:10 0 [Note] InnoDB: Number of pools: 1
2019-09-23  6:30:10 0 [Note] InnoDB: Using SSE2 crc32 instructions
2019-09-23  6:30:10 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2019-09-23  6:30:10 0 [Note] InnoDB: Completed initialization of buffer pool
2019-09-23  6:30:10 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-09-23  6:30:10 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2019-09-23  6:30:10 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-09-23  6:30:10 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-09-23  6:30:10 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2019-09-23  6:30:10 0 [Note] InnoDB: Waiting for purge to start
2019-09-23  6:30:10 0 [Note] InnoDB: 10.3.17 started; log sequence number 682398229; transaction id 1563655
2019-09-23  6:30:10 0 [Note] Plugin 'FEEDBACK' is disabled.
2019-09-23  6:30:10 0 [Note] Recovering after a crash using tc.log
2019-09-23  6:30:10 0 [ERROR] Can't init tc log
2019-09-23  6:30:10 0 [ERROR] Aborting

root@localhost:/var/log/mysql#

root@localhost:/var/log/mysql# cat error.log

2019-09-23 6:30:10 0 [Note] InnoDB: Using Linux native AIO

2019-09-23 6:30:10 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2019-09-23 6:30:10 0 [Note] InnoDB: Uses event mutexes

2019-09-23 6:30:10 0 [Note] InnoDB: Compressed tables use zlib 1.2.11

2019-09-23 6:30:10 0 [Note] InnoDB: Number of pools: 1

2019-09-23 6:30:10 0 [Note] InnoDB: Using SSE2 crc32 instructions

2019-09-23 6:30:10 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M

2019-09-23 6:30:10 0 [Note] InnoDB: Completed initialization of buffer pool

2019-09-23 6:30:10 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().

2019-09-23 6:30:10 0 [Note] InnoDB: 128 out of 128 rollback segments are active.

2019-09-23 6:30:10 0 [Note] InnoDB: Creating shared tablespace for temporary tables

2019-09-23 6:30:10 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

2019-09-23 6:30:10 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.

2019-09-23 6:30:10 0 [Note] InnoDB: Waiting for purge to start

2019-09-23 6:30:10 0 [Note] InnoDB: 10.3.17 started; log sequence number 682398229; transaction id 1563655

2019-09-23 6:30:10 0 [Note] Plugin 'FEEDBACK' is disabled.

2019-09-23 6:30:10 0 [Note] Recovering after a crash using tc.log

2019-09-23 6:30:10 0 [ERROR] Can't init tc log

2019-09-23 6:30:10 0 [ERROR] Aborting

root@localhost:/var/log/mysql#

On /var/lib/mysql folder, there is a tc.log file with 0 size. The error is solved by removing this file.

mv /var/lib/mysql/tc.log /root/

1	mv /var/lib/mysql/tc.log /root/

[…]

MariaDB DEFAULT NULL ERROR 1064 (42000)

Posted on July 27, 2019 by ServerOk

When trying to restore a MySQL databse bakcup taken on MySQL 5.7 server to MairaDB 10.1 server i get following error.

root@server:~# mysql -u vcon_user -p vcon_sbc < vcon_sbc.sql
Enter password: 
ERROR 1064 (42000) at line 287: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` ti' at line 3
root@server:~#

root@server:~# mysql -u vcon_user -p vcon_sbc < vcon_sbc.sql

Enter password:

ERROR 1064 (42000) at line 287: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'json DEFAULT NULL,

`created_at` timestamp NULL DEFAULT NULL,

`updated_at` ti' at line 3

root@server:~#

Source server

root@ip-172-31-16-209:~# mysql --version
mysql  Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using  EditLine wrapper
root@ip-172-31-16-209:~#

root@ip-172-31-16-209:~# mysql --version

mysql Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using EditLine wrapper

root@ip-172-31-16-209:~#

Destination server

root@server:~# mysql --version
mysql  Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
root@server:~#

root@server:~# mysql --version

mysql Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

root@server:~#

I opend the SQL file in editor and checked line 287, i run the SQL in mysql, that resulted in error

MariaDB [test]> CREATE TABLE `current_job_status_reports` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `jobs` json DEFAULT NULL,
    ->   `created_at` timestamp NULL DEFAULT NULL,
    ->   `updated_at` timestamp NULL DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` ti' at line 3
MariaDB [test]>

MariaDB [test]> CREATE TABLE `current_job_status_reports` (

-> `id` int(11) NOT NULL AUTO_INCREMENT,

-> `jobs` json DEFAULT NULL,

-> `created_at` timestamp NULL DEFAULT NULL,

-> `updated_at` timestamp NULL DEFAULT NULL,

-> PRIMARY KEY (`id`)

-> ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'json DEFAULT NULL,

`created_at` timestamp NULL DEFAULT NULL,

`updated_at` ti' at line 3

MariaDB [test]>

I run the command on […]