MySQL Archives - Page 3 of 5

mysqldump packet bigger than max_allowed_packet

Posted on January 1, 2019 by ServerOk

When backing up a MySQL database using mysqldump command, got following error

[root@vps189 ~]# mysqldump sok_main > sok_main.sql
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' when dumping table `wp_options` at row: 7068
[root@vps189 ~]#

[root@vps189 ~]# mysqldump sok_main > sok_main.sql

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' when dumping table `wp_options` at row: 7068

[root@vps189 ~]#

To fix, you need to edit MySQL config file

vi /etc/my.cnf

1	vi /etc/my.cnf

Add following under [mysqld] section

max_allowed_packet=2G

1	max_allowed_packet=2G

Now restart MySQL with

systemctl restart mysql

1	systemctl restart mysql

You can verify the value is changed by running following SQL command in MySQL prompt

show variables like 'max_allowed_packet';

1	show variables like 'max_allowed_packet';

Or my running following command […]

Reset MySQL 5.7 root password on Ubuntu 16.04

Posted on December 29, 2018 by ServerOk

First stop MySQL with command

service mysql stop

1	service mysql stop

Now run

mysqld_safe --skip-grant-tables

1	mysqld_safe --skip-grant-tables

On Ubuntu 16.04 server with MySQL 5.7, when i run, i get following error.

root@sok-06:~# mysqld_safe --skip-grant-tables
2018-12-29T06:13:26.191918Z mysqld_safe Logging to syslog.
2018-12-29T06:13:26.195961Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2018-12-29T06:13:26.199396Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.
root@sok-06:~#

root@sok-06:~# mysqld_safe --skip-grant-tables

2018-12-29T06:13:26.191918Z mysqld_safe Logging to syslog.

2018-12-29T06:13:26.195961Z mysqld_safe Logging to '/var/log/mysql/error.log'.

2018-12-29T06:13:26.199396Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

root@sok-06:~#

To fix, this error, create a the folder and change ownership to user mysql

mkdir /var/run/mysqld
chown -R mysql:mysql /var/run/mysqld

1 2	mkdir /var/run/mysqld chown -R mysql:mysql /var/run/mysqld

Now start a new terminal, login to MySQL with command

mysql -u root -p

1	mysql -u root -p

You can press enter for […]

Extract Backup of one database from mysqldump all databases

Posted on August 12, 2018 (August 12, 2018) by ServerOk

When you backup MySQL Server with command

mysqldump  --all-databases > all-db.sql

1	mysqldump --all-databases > all-db.sql

You get a MySQL backup file containing all databases. If you want to generate MySQL backup for single database from this file, you can run

sed -n '/^-- Current Database: `DB_NAME`/,/^-- Current Database: `/p' all-db.sql > DB_NAME.sql

1	sed -n '/^-- Current Database: `DB_NAME`/,/^-- Current Database: `/p' all-db.sql > DB_NAME.sql

See mysqldump […]

MySQL Repair

Posted on May 2, 2018 by ServerOk

Fatal error: Can’t open and lock privilege tables […]

Fatal error: Can’t open and lock privilege tables

Posted on May 2, 2018 by ServerOk

On a Cpanel Server, MySQL did not start. I checked the error log in /var/lib/mysql folder, found following error 2018-05-02 12:08:34 30796 [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‘./mysql/db’ is marked as crashed and should be repaired It says “db” table in “mysql” database got corrupted. To fix, i run following […]

MySQL create user with root privileges

Posted on April 28, 2018 by ServerOk

To create a user with “root” privileges, you need to find what privileges root have, this can be done with command

SHOW GRANTS FOR 'root'@'localhost';

1	SHOW GRANTS FOR 'root'@'localhost';

You can use following commands to create new user

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

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

Replace USER_NAME with your desired MySQL username. PASSWORD_HERE with password you need. localhost is to allow only connection from localhost, you can replace […]

You must reset your password using ALTER USER

Posted on April 23, 2018 by ServerOk

On MySQL 8, when you start MySQL, it generate a temporary root password, that you can find with command

grep 'temporary password' /var/log/mysqld.log

1	grep 'temporary password' /var/log/mysqld.log

When i try run some SQL commands after login with this temporary password, i get error ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. To fix this, […]

MySQL Permission denied

Posted on April 17, 2018 (April 17, 2018) by ServerOk

After changing MySQL data directory, i get error Apr 17 03:01:20 hon-pc-01 mysqld[25298]: 180417 3:01:20 [Note] /usr/sbin/mysqld (mysqld 10.0.34-MariaDB-0ubuntu0.16.04.1) starting as process 25297 … Apr 17 03:01:20 hon-pc-01 mysqld[25298]: 180417 3:01:20 [ERROR] mysqld: Can’t create/write to file ‘/mnt/backup/mysql/aria_log_control’ (Errcode: 13 “Permission denied”) Apr 17 03:01:20 hon-pc-01 mysqld[25298]: 180417 3:01:20 [ERROR] mysqld: Got error ‘Can’t create […]

Find MySQL Server Version

Posted on March 28, 2018 by ServerOk

To find MySQL server version, connect to MySQL, then run

select version();

1	select version();

You can also use “mysql –version”

[root@server ~]# mysql --version
mysql  Ver 14.14 Distrib 5.6.38, for Linux (x86_64) using  EditLine wrapper
[root@server ~]#

[root@server ~]# mysql --version

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

[root@server ~]#

This only show MySQL client version. If you have multiple MySQL server installed or MySQL server is running on another host, this won’t help. MySQL […]

mysql root

Posted on March 28, 2018 (April 28, 2018) by ServerOk

Change MySQL root password Reset MySQL root Password MySQL create user with root privileges MySQL […]