Stop MySQL Database systemctl stop mysqld.service Start MySQL as Safe Mode mysqld_safe –skip-grant-tables & Connect MySQL Database With Root User mysql -uroot Change Root Password use mysql; update user set password=PASSWORD(“newpassword”) where user=’root’; flush privileges; exit Restart MySQL Database systemctl stop mysqld.service systemctl start mysqld.service Connect MySQL Database With Root User With New Password mysql…
Tag: MySQL Administration
Unknown variable default character set utf8 MySQL 5.5 or MariaDB 5.5
If you get an error like “Unknown variable default character set utf8” MySQL or MariaDB 5.5 in /var/log/mariadb/mariadb.log when I restart MySQL database, you can resolve this problem with change variable name. Error You can look for error /var/log/mariadb/mariadb.log /usr/libexec/mysqld: unknown variable ‘default-character-set=utf8’ Aborting Solution
MySQL Frequently Using Commands
MySQL is one of the most using database system. Almost all web sites are using MySQL database. I wrote most using commands of MySQL database.I will share various commands using examples for MySQL database . Connect MySQL [root@testdb ~]# mysql [root@testdb ~]# mysql -h hostip [root@testdb ~]# mysql -h hostip -u username [root@testdb ~]# mysql…
MySQL Database Import And Export Operations
Take Backup With Data Open Command Prompt (cmd) # mysqldump -u dbusername -p db_name > /tmp/db_fullbackup.sql Take Only Metadata Backup # mysqldump -u dbusername -p –no-data db_name > /tmp/db_backupmetadata.sql or # mysqldump -u dbusername -p -d db_name > /tmp/db_backupmetadata.sql Restore Database Metadata From Backup # mysql -u dbusername -p db_name < /tmp/db_backupmetadata.sql Restore Database From...
MySQL Data File Size Decrease
If ibdata1 data file size is too big, you can decrease this file. My scenario is that my database have a table and size is 100 GB, I want to take backup and delete all data with decrease datafile size. If I use truncate command, this is not decrease ibdata1 size. I can use backup…