The Problem If the MySQL password contains a dollar sign character '$', PHP is unable to connect and issues an error like: Access denied for user 'username'@'hostname' (using password: YES) Connections via other clients work correctly. The Solution PHP interprets dollar signs inside of double quotes as starting a variable name, which will be expanded. This expansion will change the password sent to MySQL. There are 3 ways to avoid this problem: Escape the character with a … [Read more...] about “Access denied for user ‘username’@’hostname’ (using password: YES)” – Error while connecting MySQL with PHP
mysql
How to Restrict MySQL User Creation with Blank Password
Question: How can we not allow user creation with a blank password in MySQL? Using a blank password for a user is always a no go. In order to restrict user creation with blank password follow the steps given below: Before MySQL 5.6 In case of MySQL version below 5.6, this can not be achieved completely. But there are settings that can be used to come closer to our objective. Set the below SQL mode on the MySQL instance: SET SQL_MODE = NO_AUTO_CREATE_USER Using NO_AUTO_CREATE_USER … [Read more...] about How to Restrict MySQL User Creation with Blank Password
Beginners Guide to MySQL Data Types
Data Types: Overview In MySQL, the available data types can be grouped into four major categories: Numeric: Numeric values Binary: Binary data strings Character: Text strings Temporal: Time and date values Within each category, there are numerous specific data types that use varying amounts of memory and disk space, and thus have varying effects on performance. Choosing the best data type for the column has a rather small effect on performance in an individual record, but as the … [Read more...] about Beginners Guide to MySQL Data Types
“expect” script to provide password to mysql_config_editor
Question: How to provide a password to mysql_config_editor without exposing it on the command line and without having to be present to type it? Use an expect script. For example: #!/usr/bin/env expect spawn mysql_config_editor set --skip-warn --login-path=client --user=root --password expect "Enter password: " send "$env(my_password)\r" expect eof The password can be stored in whatever location is considered secure enough for the operation that is accessible by the script. … [Read more...] about “expect” script to provide password to mysql_config_editor
Understanding MySQL Query Cache
What is MySQL Query Cache MySQL server features Query Cache Feature for a long time. When in use, the query cache stores the text of a SELECT query together with the corresponding result that is sent to a client. If another identical query is received, the server can then retrieve the results from the query cache rather than parsing and executing the same query again. It caches the full result set produced from a SELECT query: Queries executed must be identical. Cache is stored in … [Read more...] about Understanding MySQL Query Cache
MySQL – How to Backup User Privileges as CREATE USER and/or GRANT Statements
Question: How to backup the users and their privileges as CREATE USER and/or GRANT statements? The method you can use depends on the version of MySQL you are using. MySQL 5.7 and Later In MySQL 5.7 and later, the recommended way to backup the users is to use mysqlpump. This has built-in support for backing up the users and their privileges through the --users option: $ mysqlpump --exclude-databases=% --users Note: You may need to add connection parameters such as --socket, --host, … [Read more...] about MySQL – How to Backup User Privileges as CREATE USER and/or GRANT Statements