thecweb.com

Tag: sql

  • thecweb to DB DP

    thecweb to DB DP

    I finally decided on CentOS and Cockpit for the VM Host. Which is quite surprising. I have naturally used RedHat Enterprise and clones at work for quite some time, but I haven’t given them another look since they first started with Cockpit, and man is it slick now. Some clarification I needed to arrive at this decision is what CentOS actually IS. It is basically a stable release of RHEL. The next upcoming release, so they give that out for free for people to test before they release it to paying customers. I did not know that it is made by RH engineers, and I never really saw myself ever using a RH derived distro since I abandoned them in the late 90s.

    Anyhoo, back to what this post is actually about, the DB migration. I was trying to come up with a clever name that rhymed with DB and DP came to mind, so we get this completely tasteless image and server name DBDP. If you are not familiar with the reference – then good – you’ve lived a good life.

    I configured the new VM Guest with 8 cores and 32 GB of RAM. This is probably overkill, but it will allow me to do stupid things and “probably” not take out my website db in the process.

    Ubuntu Server 24 LTS is the OS, and I’m switching from mysql to mariadb. Honestly, I don’t know why I even chose mysql. I wouldn’t have if I remembered that it was now owned by Oracle, part of the Sun acquisition. It is my opinion that Oracle was and continues to be everything that MS was made out to be during the anti-trust cases of the 90s. Actually, I just googled and it wasn’t settled until 2001, but it started in 1990.

    I just used the regular server netinstall iso I used for the old db server, only the 24 version, and so far I’ve just the mariadb-server package. Side note, /var/log/apt/history.log next time you can’t remember what you’ve installed with apt. I setup a winscp connection for root and copied over the keys for password-less login. Added a rule for mysql in the fancy-shmancy pit of cocks.

    Fire up DBeaver and connect to mariadb as root over SSH, so I can create a dev account on the DB.

    Which of course did not allow me to connect. Mariadb by default doesn’t even allow local connections over tcp/ip, I find after much confusion. So I add this to /etc/mysql/my.cnf.

    [mysqld]
skip-networking=0
skip-bind-address

    Same as mysql, accounts are tied to hosts, and root is tied to local host, so I still won’t be able to connect with root even over ssh, apparently. So I’ll create a development user that is close to root, and I managed to do it without much googling thanks to an earlier post.

    CREATE USER 'cwebdev'@'%' IDENTIFIED BY '*****';

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'cwebdev'@'%' WITH GRANT OPTION;

    Yay! DBeaver is connecting.

    And now it’s a few minutes until one am, and I’m hungry. Off to WhataBurger and then I’ll dick-around with loading info from that kasa power strip.

  • SQL – notes

    create user

    CREATE USER '<username>'@'<connect from hostname>' IDENTIFIED BY '<password>';

    grant access

    # basic syntax
CREATE USER '<username>'@'<connect from hostname>' IDENTIFIED BY '<password>';

# as close to root without being root
# probably shouldn't be giving any user this much access
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO '<username>'@'<connect from hostname>' WITH GRANT OPTION;

    database

    create database <databasename>;

show databases;

    Inserting data

    # INSERT INTO Syntax
# It is possible to write the INSERT INTO statement in two ways:
# 1. Specify both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

# 2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the # INSERT INTO syntax would be as follows:

INSERT INTO table_name
VALUES (value1, value2, value3, ...);