Cara menggunakan mysql 5.7 upsert

MySQL 5.7 installation in CentOS 7

sudo su

# Remove MariaDB packages
yum list installed | grep -i maria
yum remove mariadb.x86_64
yum remove mariadb-libs.x86_64

# Download MySQL 5.7 RPM tar
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar
mkdir mysql-5.7-rpm-packages
tar -xvf mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar -C mysql-5.7-rpm-packages
cd mysql-5.7-rpm-packages/

# Don't use 'yum install *.rpm'. It will not work and give error messages
yum install mysql-community-{server,client,common,libs}-* --exclude='*minimal*'
# Start MySQL
systemctl start mysqld
# Enable mysqld service in startup
systemctl enable mysqld

# Get temporary password of root
grep 'A temporary password' /var/log/mysqld.log |tail -1

# Make MySQL secure installation
mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 

The existing password for the user account root has expired. Please set a new password.

New password: 

Re-enter new password: 
 ... Failed! Error: Your password does not satisfy the current policy requirements

New password: 

Re-enter new password: 
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 100 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y

New password: 

Re-enter new password: 

Estimated strength of the password: 100 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done! 

Uninstall Password Validation Plugin

uninstall plugin validate_password;

Edit my.cnf for Confluence applications

# Add below line in [mysqld] section
transaction-isolation=READ-COMMITTED

Change a MySQL Data Directory to a New Location on CentOS 7

https://gist.github.com/linuxkathirvel/973de61756e742e1e4e33ce36707b1d2

References

1. 'yum install' sample is wrong
2. How to Install MySQL 5.7 on CentOS/RHEL 7/6, Fedora 27/26/25

How to Install MySQL 5.7 on Linux CentOS and Ubuntu

On this page

1. Step 1 - Add New Repository
2. Step 2 - Install MySQL 5.7
3. Step 3 - Start MySQL and Enable Start at Boot Time
4. Step 4 - Configure the MySQL Root Password
5. Step 5 - Testing
6. Reference

MySQL is an open source relational database management system developed by Oracle corporation. The source code is released under GNU General Public License, and for proprietary, there is a paid edition that offers additional functions. MySQL is an important component of the LAMP and LEMP stacks. Many applications use these stacks with MySQL as the database system.

In this tutorial, I will guide you how to install and configure the new stable MySQL 5.7 version on CentOS 7 server and Ubuntu 16.04 server.

Below is what we will do for this purpose.

1. Add a new repository that contains the latest MySQL version.
2. Install MySQL 5.7 stable version.
3. Add MySQL to the boot time.
4. Reset the default MySQL root password.
5. Testing to create new user and database.

Step 1 - Add New Repository

MySQL provides a repository for several Linux distributions including rpm and deb based distributions that contain the latest stable MySQL release. So we need to add a new MySQL repository to the system to proceed.

Add the new MySQL repository to the CentOS 7 server with this yum command.

yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

You will be asked to add a new repository, type 'y' and press 'Enter' to confirm.

For Ubuntu, download the MySQL deb package repository file and install it with the dpkg command and then update the repository.

wget https://dev.mysql.com/get/mysql-apt-config_0.8.3-1_all.deb
dpkg -i mysql-apt-config_0.8.3-1_all.deb
apt-get update

During the package repository installation, you will be asked which MySQL version you want to install and which additional MySQL tools you want to install. I will leave all at the default, so just choose 'Ok' and press 'Enter'.

A new repository has been added to the system.

Step 2 - Install MySQL 5.7

The new MySQL repository is available on the system now and we are ready to install MySQL 5.7 latest stable version from the repository. The package name is 'mysql-community-server'.

On CentOS, install 'mysql-community-server' with yum.

yum -y install mysql-community-server

For Ubuntu, please use this apt command.

apt-get install -y mysql-community-server

Note:

On Ubuntu, you will be asked for a new MySQL root password, type in and repeat a strong password.

Retype your password.

MySQL 5.7 installed.

Step 3 - Start MySQL and Enable Start at Boot Time

After installing MySQL, start it and add MySQL to start at boot time automatically with the systemctl command.

For CentOS server use 'mysqld' service.

systemctl start mysqld
systemctl enable mysqld

For Ubuntu we can use 'mysql' service.

systemctl start mysql
systemctl enable mysql

MySQL started, it's using port 3306 for the connection, you can check that on both Ubuntu and Centos server with the netstat command.

netstat -plntu

If you want to check if the mysql service automatically starts at the boot time or not, you can use the is-enabled option below.

For CentOS.

systemctl is-enabled mysqld

For Ubuntu.

systemctl is-enabled mysql

enabled result = the service is added to start at boot time.

Step 4 - Configure the MySQL Root Password

MySQL 5.7 is installed and started. As you can see, the MySQL root password for Ubuntu has been configured during the installation process, so we do not need to configure or reset the password on Ubuntu. But this is not the case on CentOS.

On CentOS 7, MySQL will generate a strong default password when MySQL is started the first time, the default password is shown in the mysqld.log file. You can use the grep command below for showing the default MySQL password.

grep 'temporary' /var/log/mysqld.log

You will see default MySQL root password, and we should set a new default password now.

Connect to the MySQL shell with the default password.

mysql -u root -p
TYPE DEFAULT PASSWORD

Now replace the default password with a new and strong password, in my case, I will set the password '[email protected]' with the MySQL query below.

ALTER USER 'root'@'localhost' IDENTIFIED BY '[email protected]';
flush privileges;

The default password has been changed.

Note:

You can use this query to reset the MySQL root password CentOS and Ubuntu at any time later as well.

Step 5 - Testing

In this step, we will try to connect to the MySQL shell with the new password and then create a new database and user.

Connect to the MySQL shell with the MySQL root password, using the 'mysql' command.

mysql -u root -p
TYPE NEW PASSWORD: [email protected]

Create a new database named 'hakaselabs'. Of course, you can choose your own name here.

create database hakaselabs;

And create a new user with the name 'hakase' and the password is '[email protected]' (or whatever userrname and password you want to use).

create user [email protected] identified by '[email protected]';

Now grant privileges for the new database to the new user.

grant all privileges on hakaselabs.* to [email protected] identified by '[email protected]';
flush privileges;

Exit from the MySQL shell and try connect again with the new user.

mysql -u hakase -p
TYPE THE PASSWORD: [email protected]

Show the database list under the 'hakase' user.

show databases;

You can see a database named 'hakaselabs'.

MySQL 5.7 has been installed successfully, it has been started and configured to automatically start at boot time. And we created a new user and database and connected successfully to the MySQL shell with our new user.

Reference

https://dev.mysql.com/doc/refman/5.7/en/linux-installation.html