Error while trying to access phpmyadmin from within cpanel : #1045 Cannot log in to the MySQL server

ARUN Posted in CPANEL FRONTEND, MYSQL
0

Getting the following error while trying to access phpmyadmin from within cpanel :

#1045 Cannot log in to the MySQL server

Solution :

Please change the cpanel username from within cpanel. make sure that “Allow MySQL password change” check box is selected while changing the password.

Error: Connection to the database server failed: Table ‘mysql.servers’ doesn’t exist on plesk server

ARUN Posted in MYSQL, PLESK BACKEND, PLESK FRONTEND
0

Getting the error  Error: Connection to the database server failed:  Table ‘mysql.servers’ doesn’t exist

when I try to add a database user to a database.

Solution :

Execute the command after login to shell
mysql_fix_privilege_tables --user=admin --password=<admin passord> --verbose
Now try adding the users

MYSQL root password less login

ARUN Posted in MYSQL
0

vi /root/.my.cnf

[client]
user=”root”
password=”your_mysql_password”

ERROR 1153 (08S01) at line 16985: Got a packet bigger than ‘max_allowed_packet’ bytes

ARUN Posted in MYSQL
0

While trying to restore a mysql database I am getting the following error.

[email protected] [/var/lib/mysql]# mysql a24uall_data </home/a24uall/dbuploaded.sql
ERROR 1153 (08S01) at line 16985: Got a packet bigger than ‘max_allowed_packet’ bytes
Solution :
In my.cnf and add the following line :
set-variable = max_allowed_packet=32M     and restart mysql
/etc/init.d/mysql restart

mysql prompting for password

ARUN Posted in MYSQL
0
vi /root/.my.cnf
[mysql]
user = admin
password = yourmysqlpassword
[mysqldump]
user = admin
password = yourmysqlpassword

What is the root mysql password in plesk server.

ARUN Posted in MYSQL, PLESK BACKEND, PLESK FRONTEND
0

Parallels Plesk Panel renames ‘root’ account to ‘admin’ when the Panel is installed.

To get root privileges simply login with the ‘admin’ username instead. The password is the same as the admin password in Plesk. You can find it in /etc/psa/.psa.shadow.

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

ARUN Posted in ERROR, MYSQL, PLESK BACKEND, PLESK FRONTEND
0

While trying to use mysql in plesk I am getting the following error :

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

Solution :

echo “use psa; truncate lockout;” | mysql -u root -p`cat /etc/psa/.psa.shadow`     —> this can be a solution, but I have not tested that with 100% success

Check mysql engine support

ARUN Posted in MYSQL
0

mysql> show engines;
+————+———-+—————————————————————-+
| Engine | Support | Comment |
+————+———-+—————————————————————-+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
| InnoDB | DISABLED | Supports transactions, row-level locking, and foreign keys
===

Mysql Timeout

ARUN Posted in MYSQL
0

interactive_timeout=75 ————> The number of seconds the server waits for activity on an interactive connection before closing it.

wait_timeout=25 ——————-> The number of seconds the server waits for activity on a noninteractive connection before closing it. This timeout applies only to TCP/IP and Unix socket file connections, not to connections made via named pipes, or shared memory.

connect_timeout=10 ——————-> The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake.

Mysql remote connection

ARUN Posted in MYSQL
0
To grand remote privilage to a particular database from a particular IP : GRANT ALL PRIVILEGES ON database_name.* TO USERNAME@IP IDENTIFIED BY "PASSWORD";
To grand remote privilege to a particular database from all Ips : GRANT ALL PRIVILEGES ON database_name.* TO USERNAME@'%' IDENTIFIED BY "PASSWORD";
To grand remote privilage to all database on that server from a particular IP : GRANT ALL PRIVILEGES ON database_name.* TO USERNAME@IP IDENTIFIED BY "YOUR_PASSWORD";

Grand remote access from ONE IP to ONE DATABASE on a remote server
GRANT ALL PRIVILEGES ON database_name.* TO [email protected]_ADDRESS IDENTIFIED BY 'YOUR_PASSWORD';

MYSQL OPTIMIZATION

ARUN Posted in MYSQL, OPTIMIZATION
0

vi /etc/my.cnf

Delete what ever present inside and paste the below in that.

[mysqld]
socket=/var/lib/mysql/mysql.sock
#skip-locking — disabled by default
#skip-innodb — only when all tables are MyISAM
skip-name-resolve # saves dns resolution time.
query_cache_limit=1M
query_cache_size=64M
query_cache_type=1
max_connections=500
interactive_timeout=75
wait_timeout=25
connect_timeout=10

thread_cache_size=128
key_buffer=256M
sort_buffer_size=2M
read_buffer_size=2M ## sort+read x connections + key = memory usage
join_buffer=2M
max_allowed_packet=16M
table_cache=1024
record_buffer=2M
thread_concurrency=8
myisam_sort_buffer_size=64M
#log-bin
server-id=1

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

/etc/rc.d/init.d/mysql restart

Error ” Too many connections”

ARUN Posted in MYSQL
0

Go to my.cnf

Increase  max_connection

Default value is  100

MYSQL upgrade

ARUN Posted in MYSQL
0

vi /var/cpanel/cpanel.config

EDIT  mysql-version=5.1     (You can use 5.1 or 5.0)

/scripts/mysqlup

Please make sure to run

/scripts/easyapache

Command to show databases

ARUN Posted in MYSQL
0

show databases;

Remote mysql connection command

ARUN Posted in MYSQL
0

mysql -h remote IP -u username -p

Press Enter and it will ask for password.

Or

mysql -h remote IP -u username -ppassword  –> ( Type the password after “-p” without any space )

Eg : mysql -h 192.168.0.1 -u webhost_a24uall -p