Categories
Linux Red Hat Ubuntu

Vi (or Vim) session no longer responding?

If your Vi or Vim session is hanging and is not responding to anything, then it might be because you pressed CTRL+S accidentally (out of habbit). To fix this, just press CTRL+Q to unblock the session.

Categories
Dell XPS15 Laptop Linux Ubuntu

No wifi or no external monitor on Dell XPS-15

If your wifi card is not available after booting your Dell XPS-15 laptop (with Ubuntu 20.04 or 22.04), or your external monitor refuses to display anything, please try the following:

start up your laptop without your power supply plugged in, connect with wifi, and/or your external monitor and only then plug in your power supply.

This fix works everytime for me.

Categories
Linux

Ubuntu: reset monitor settings to default

If you want to reset your monitor settings to default through the terminal (your second screen is broken and the settings are ‘shown’ there and you don’t want to restart your laptop), you can use the following command:

xrandr -s 0
Categories
Linux Ubuntu

Ubuntu Server – CTRL-ALT-DEL

If you press CTRL-ALT-DEL on a Ubuntu Server the server will be shut down without asking any confirmation or password. This is of course not a good idea.

To disable this, enter the following commands as root:

systemctl mask ctrl-alt-del.target
systemctl daemon-reload
Categories
Databases DBeaver MySQL

DBeaver: refresh data not showing

If you try to refresh the data in a table while using DBeaver, and no changes are shown, it could be that you are in transactional mode.

To fix this, click on the menu ‘Database’ and select ‘Transaction mode’. Select ‘Auto-commit’ and ‘Serializable’.

Categories
Linux

Linux: using tmux to start server processes

If you want to start a process or command on a linux server and want to return later to check if it ran ok, or if it is still running, then the tmux tool is very handy.

Tmux can do many more things (like split a terminal window in multiple panes), but that is outside the scope of this article.

To start a new session on your server (once you are connected to the server with ssh), type:

tmux new -s mysession

where ‘mysession’ is the name you want to use

Now start whatever command you want.

If you want to leave your session, but keep everything running so you can check back at a later time, press Ctrl-b d.

To list all detached sessions, use the following command:

tmux ls

No you can reattach to a session using the following command:

tmux attach -t mysession

Categories
Backup Linux MySQL

MySQL: backup your databases

I have put an interesting bash script to backup your database on my git page. You can find it here on https://github.com/afeys/backupmysql/.

Feel free to use and modify it however you please. If you add interesting functionality, or find any annoying bugs, then some feedback through github would be nice.

You can use this script interactively by just typing ./backupmysql.sh in the scripts folder, or you can find out about all the parameters it accepts by using the -h switch.

The script also allows you to create config files with all necessary info to backup a database and then run or schedule it with

/home/backupadmin/scripts/backupmysql.sh -c /home/backupadmin/configs/mycompanydatabase.config > /var/log/backupdatabase.log

Where mycompanydatabase.config is a textfile containing something like this:

databasehost = 10.10.10.1
databaseuser = backupuser
databasepassword = myverysecretdatabasepassword
backupmode = DATA
targetdirectory = /mnt/backupserver/backups/databases
tempdirectory = /tmp
comment = daily backup

Categories
Linux MySQL PHP

MySQL: get value of last auto-increment field

If you use an auto increment field (autonumbering) in your database table, you can find the last generated number by performing the following query :

select last_insert_id();

This will get the last id for the current connection. If you close the connection you can’t get the number again, and it will only return the last number for *your* connection. It is kept on a per-connection basis.

Categories
Linux MySQL

Using MySQL (basics)

This article assumes you are using linux, and mysql has already been installed and configured.

Starting the interactive environment

Start a linux terminal (CTRL+ALT+T) and type the following:

mysql --user=<username> --password=<password>

Where <username> is the name of the user that has access to mysql, and <password> is his/her/their password.

Creating a database

Once you have started the interactive environment as explained above, you can start creating databases by typing the following command:

create database mydatabase;

Please replace mydatabase with the name of the database you want to create.

Defining permissions

To grant a user all rights on all databases, you can use the following command:

grant all privileges on *.* to 'username'@'localhost'  identified by 'mypassword' with grant option;

Of course this is not the safest setup, you should always limit the privileges as much as possible. So to grant a user only select on all databases, you could use:

grant select on *.* to 'username'@'localhost'

Suppose you want to give select access on one specific table in one database, to a user coming from any host:

grant select on `mydatabase`.`mytable` to 'username'@'%';

Notice the backticks around the databasename, and the tablename. And the % sign to indicate any host.

Showing permissions for a user

show grants for username@localhost;

Using a database

Before you can access a database, you have to tell MySQL which database you want to use:

use mydatabase;

Replace mydatabase with the name of the database you want to access.

Creating a table

Suppose you want to create a table ‘products’ in database ‘mydatabase’ with the following fields:

'id'          : a unique recordkey filled automatically
'name'        : the name of the product
'description' : a longer text describing the item
'stock'       : a number, no decimals
'price'       : number, two decimals
'dateupdate'  : date field

The primary index will be on the unique id, a secondary index will be on name.

To create this table, use the following command:

use mydatabase;
create table products (
id INT NOT NULL AUTO_INCREMENT,
name CHAR(255),
description TEXT,
stock BIGINT,
price DECIMAL(9,2),
dateupdate DATE,
PRIMARY KEY (id), KEY(name)
);

A short explanation:

the field ‘id’ is created as an INTeger, can NOT be NULL, and is AUTOmatically INCREMENTed, ‘name’ can contain 255 CHARacters, ‘description’ can contain a large TEXT, ‘stock’ is a BIG INTeger, so it can’t have a decimal point, ‘price’ is 9 characters long, 2 after the decimal point.

List all tables in a database

To list all tables in database mydatabase use the following commands:

use mydatabase;
show tables;

Show the structure of a MySQL table

To show the structure of table ‘products’ use the following command:

use mydatabase;
describe products;

This will get an overview similar to the one below:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      |      | PRI | NULL    | auto_increment |
| name       | varchar(255) | YES  | MUL | NULL    |                |
| description| text         | YES  |     | NULL    |                |
| stock      | bigint(20)   | YES  |     | NULL    |                |
| price      | decimal(9,2) | YES  |     | NULL    |                |
| dateupdate | date         | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

Backup and restore of MySQL databases

There will be a separate post for this, including a handy little script to automate this.

A simple way to backup the database is type the following command on the bash prompt (Not inside the mysql interactive environment)

mysqldump mydatabase > backup_mydatabase.sql

Replace ‘mydatabase’ by the name of the database you want to backup, and ‘mydatabase.sql’ by the name of the backupfile.

To restore this you open the mysql interactive environment, create an empty database as explained below, select the database and then process the backup file. You can do this as follows:

create database mydatabase;
use mydatabase;
source backup_mydatabase.sql

Replace ‘mydatabase’ with the name of your database and ‘mydatabase.sql’ with the name of you backup file. It is best to use the full path here (e.g. /home/dbadmin/backups/backup_products_database.sql)