How to auto login in MySQL from a shell?

When you run MySQL commands MySQL, mysqlcheck, mysqdump and psql, psqldump, etc; they will pick username & password from this file if you do not provide them as argument (-u and -p). It can save you time.

Of course, if you specify username and password explicitly as part of the command’s arguments, they will be used.

.my.cnf ( for MySQL client )

[clienthost1]   # Note: client + host1
user=myuser
password=mypass
database=dbname
host=server.location.com

How to auto login in Postgres from a shell?

When you run MySQL commands MySQL, mysqlcheck, mysqdump and psql, psqldump etc; they will pick username & password from this file if you do not provide them as argument (-u and -p). It can save you time.

Of course, if you specify username and password explicitly as part of the command’s arguments, they will be used.

.pgpass ( for psql client )

test.net:5432:testdb:testuser:testpass
test1.net:5432:testdb1:testuser1:testpass1
test2.net:5432:testdb2:testuser2:testpass2

How to install, configure, create user and database with permissions – MySQL

MySQL is an open-source relational database management system. Its name is a combination of “My”, the name of co-founder Michael Widenius’s daughter, and “SQL”, the abbreviation for Structured Query Language.

Download and Install MySQL from the following link

https://dev.mysql.com/downloads/

rpm -ih mysql80-community-release-el7-3.noarch.rpm
yum update -y
yum install -y mysql-server

Configure

systemctl enable mysqld
systemctl start mysqld
mysql_secure_installation
# default password
grep -oP 'temporary password(.*): \K(\S+)' /var/log/mysqld.log

Create user and database with permissions

mysql -u root -p
# mysql > 
CREATE DATABASE dbname;
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL PRIVILEGES ON dbname.* TO 'myuser'@'%';
FLUSH PRIVILEGES;

Starting with MySQL 8 you no longer can (implicitly) create a user using the GRANT command. Use CREATE USER instead, followed by the GRANT statement:

CREATE USER 'root'@'%' IDENTIFIED BY 'root';
GRANT ALL PRIVILEGES ON . TO 'root'@'%' WITH GRANT OPTION;

How to install, configure, create user and database with permissions – PostgreSQL

postgresql

PostgreSQL: The World’s Most Advanced Open Source Relational Database

PostgreSQL is arguably the most advance and powerful opensource enterprise class relational database system. It is the object relational database system and provides the most standard compliant system for the Database designers. It provides the complete support for reliable transactions that is (ACID complaint) where ACID stands for Atomicity, Consistency, Isolation and Durability.

Its advance underlying technology makes it extremely powerful and programmable. Support for concurrency is one of its key feature. It is one of the most important technology you will learn and will greatly affect the way you work with Databases. It is the ultimate RDBM system which will allow you to create complex web apps which works flawlessly even for very large number of users.

1. Download and Install Postgresql from the following link

https://www.postgresql.org/download/

2. Configure

service postgresql initdb
systemctl enable postgresql
systemctl start postgresql

Edit the file /etc/postgresql/8.4/main/pg_hba.conf and replace ident or peer by either md5 or trust, depending on whether you want it to ask for a password on your own computer or not. Then reload the configuration file with:

/etc/init.d/postgresql reload

pg_hba.conf

local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            password  
# IPv6 local connections:
host    all             all             ::1/128                 password

3. Create user and database with permissions

sudo -u postgres psql
postgres=# create database mydb;
postgres=# create user myuser with encrypted password 'mypass';
postgres=# grant all privileges on database mydb to myuser;

adminer setup script

Adminer (formerly phpMinAdmin) is a full-featured database management tool written in PHP. Conversely to phpMyAdmin, it consists of a single file ready to deploy to the target server. Adminer is available for MySQL, MariaDB, PostgreSQL, SQLite, MS SQL, Oracle, Firebird, SimpleDB, Elasticsearch, and MongoDB.

install script

sudo mkdir /usr/share/adminer
sudo wget "http://www.adminer.org/latest.php" -O /usr/share/adminer/latest.php
sudo ln -s /usr/share/adminer/latest.php /usr/share/adminer/adminer.php
echo "Alias /adminer.php /usr/share/adminer/adminer.php" | sudo tee /etc/apache2/conf-available/adminer.conf
sudo a2enconf adminer.conf
# restart apache/httpd

mysqld – Got packet bigger than ‘max_allowed_packet’ bytes when dumping table `memcache` at row

  1. Add --max_allowed_packet=512M to your mysqldump command.
  2. Or add max_allowed_packet=512M to [mysqldump] the section of your my.cnf

Note: it will not work if it is not under the [mysqldump] section…