On Debian/Ubuntu MySQL root user can only login using socket. This is done with Plugin. MariaDB [(none)]> select User, Plugin from mysql.user; +———–+————-+ | User | Plugin | +———–+————-+ | root | unix_socket | | sserverok | | +———–+————-+ 2 rows in set (0.00 sec) MariaDB [(none)]> To fix this, first set a password for … Read more
MySQL is one of the most popular relational database management systems in use today, powering many popular websites and applications. To get the best performance, you need to optimize the MySQL database. innodb_buffer_pool_size – Increase the buffer pool size. The buffer pool is where MySQL stores data and indexes in memory, so increasing its size … Read more
MySQL Installation MySQL User Management MySQL Optimize MySQL Backup/Restore MYSQL Commands MySQL Utils MySQL GUI MySQL Errors
To reset MySQL root password, stop MySQL service mysql stop Start MySQL with mysqld_safe –skip-grant-tables Start another terminal, login to MySQL as root mysql -u root Run following commands to change password. update mysql.user set password=PASSWORD(“PASSWORD_HERE”) where User=’root’; flush privileges; quit Now you need to kill running MySQL processes and start MySQL as normal. Restart … Read more
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; 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; To allow a user to create another user GRANT GRANT OPTION ON *.* … Read more
On Debian/Ubuntu by default MySQL root user can only login from local accounts. MariaDB [(none)]> select plugin from mysql.user where user=’root’; +————-+ | plugin | +————-+ | unix_socket | +————-+ 1 row in set (0.00 sec) MariaDB [(none)]> To enable login from phpMyAdmin, you need to set plugin to “mysql_native_password”. To do this, login to … Read more
vi ~/.my.cnf Add following content [client] user = root password = “DBPASSWORD” [mysqladmin] user = root password = “DBPASSWORD” [mysqldump] user = root password = “DBPASSWORD” Replace DBPASSWORD with your MySQL root password. Now set permission for this file to 600, so no user other than root can see it. chmod 600 ~/.my.cnf
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 … Read more
When repairing a large database, i got following error [root@server root]# myisamchk –silent –force –fast –update-state \ –key_buffer_size=64M –sort_buffer_size=64M \ –read_buffer_size=1M –write_buffer_size=1M /var/lib/mysql/databasename/*.MYI -bash: /usr/bin/myisamchk: Argument list too long To fix the error, run SOLUTION 1 find /backup/mysql/bizhat_s2 -type f -print0 -name ‘*.MYI’ | xargs -0 myisamchk –force –fast –update-state –key_buffer_size=64M –sort_buffer_size=64M –read_buffer_size=1M –write_buffer_size=1M SOLUTION … Read more