Yearly Archives: 2023

MySQL copy the contents of one table into another

19 April 2023 in Applications tagged distinct / INSERT / INSERT INTO / mysql / SELECT DISTINCT by Tux

MySQL is a popular open-source relational database management system that allows users to store and retrieve data from a database. The INSERT INTO command is used to insert data into a MySQL database, while the SELECT statement is used to retrieve data from a database. In this blog post, we will explore the MySQL command INSERT INTO tableA SELECT DISTINCT * FROM tableB“.

Explanation:

The “INSERT INTO tableA SELECT DISTINCT * FROM tableB;” command is used to insert unique rows from tableB into tableA. Let’s break down this command to understand its components:

INSERT INTO tableA: This component specifies the table that we want to insert data into. In this case, we are inserting data into tableA.
SELECT DISTINCT : This component selects all the columns () from tableB and removes any duplicates using the DISTINCT keyword.
FROM tableB: This component specifies the table from which we want to select data. In this case, we are selecting data from tableB.

Putting it all together, the command INSERT INTO tableA SELECT DISTINCT * FROM tableB; selects all the unique rows from tableB and inserts them into tableA.

Let’s look at an example to understand this command better. Suppose we have two tables, tableA and tableB, with the following data:

tableA:

id	name	age
1	John	20
2	Peter	25

tableB:

id	name	age
1	John	20
2	Peter	25
3	Sarah	30
4	Rachel	35

If we run the command INSERT INTO tableA SELECT DISTINCT * FROM tableB;, it will insert the unique rows from tableB into tableA. Since tableA already contains the rows for John and Peter, only the rows for Sarah and Rachel will be inserted into tableA. Therefore, the resulting tableA will look like:

tableA:

id	name	age
1	John	20
2	Peter	25
3	Sarah	30
4	Rachel	35

Conclusion:

The MySQL command “INSERT INTO tableA SELECT DISTINCT * FROM tableB;” is a powerful tool for inserting unique rows from one table into another. It can be helpful when you have two tables with overlapping data and want to consolidate the data into a single table. Understanding how this command works allows you to manage your MySQL databases and optimize your data storage effectively.

How to empty the gnome tracker3 cache?

16 April 2023 in Bash / GNU/Linux tagged bash / clear cache / daemon / free space / gnome / GNU/Linux / tracker3 / ubuntu by Tux

To empty the cache of gnome tracker3, you can follow the steps below:

Open a terminal window by pressing Ctrl + Alt + T.

Type the following command to stop the tracker daemon:

tracker3 daemon -t;

Type the following command to clear the tracker database:

tracker reset --filesystem;

This command will remove all indexed data from the tracker and clear its cache. (Remove filesystem indexer database)

Restart the tracker daemon by typing the following command:

tracker daemon -s ;

This will start the tracker daemon again, and it will begin to rebuild its database and cache.

After following these steps, the cache of gnome tracker3 will be emptied.

Execution example:

$ tracker3 daemon -t
Found 1 PID…
  Killed process 13705 — “tracker-miner-fs-3”
$ tracker3 reset --filesystem
Found 1 PID…
  Killed process 13705 — “tracker-miner-fs-3”
$ tracker3 daemon -s
Starting miners…
  ✓ File System

Compressing mysqldump with pipe: MySQL

13 April 2023 in Bash / GNU/Linux tagged compress / gzip / mysql / mysqldump by Tux

When working with MySQL databases, it’s common to create backups of the database using the mysqldump utility. However, these backups can often take up a significant amount of disk space, especially for large databases. One way to reduce the size of these backups is to compress them using a compression algorithm. In this post, we will explore how to compress a mysqldump using a pipe.

First, let’s review the basic syntax for creating a mysqldump:

mysqldump -u [username] -p [database_name] > [backup_file].sql

This command will create a plain-text backup file of the specified database, which can then be restored using the mysql command. However, this backup file can be quite large, especially for large databases.

To compress the backup file, we can use a pipe to redirect the output of the mysqldump command to a compression utility. One common compression utility is gzip, which uses the gzip algorithm to compress files. Here’s how we can use gzip to compress the mysqldump:

mysqldump -u [username] -p [database_name] | gzip > [backup_file].sql.gz

In this command, we use the | symbol to pipe the output of the mysqldump command to the gzip command. The > symbol is then used to redirect the compressed output to a file with a .sql.gz extension.

The resulting backup file will be compressed using the gzip algorithm, which typically results in significant reduction in file size. To restore the backup, we can use the following command:

gunzip < [backup_file].sql.gz | mysql -u [username] -p [database_name]

In this command, we use the gunzip command to decompress the compressed backup file, which is then piped to the mysql command to restore the database.

In conclusion, compressing a mysqldump using a pipe is a simple and effective way to reduce the size of backup files. By using a compression utility such as gzip, we can significantly reduce the amount of disk space required to store backups, while still being able to restore the database using standard MySQL commands.

Cloudflare API DNS Update

10 April 2023 in Applications / Bash / Firewall / GNU/Linux / HowTos tagged api / cloudflare / DNS / update DNS record / what is my ip by Tux

Cloudflare is a content delivery network (CDN) that provides a wide range of services, including domain name system (DNS) management. The Cloudflare API allows developers to programmatically manage DNS records, making it possible to automate updating DNS records. This blog post will explain how to use the Cloudflare API to update a DNS record.

Log in to Cloudflare and get your Global API Key

First, log in to your Cloudflare account and obtain your Global API Key. You can obtain your Global API Key by navigating to the URL: https://dash.cloudflare.com/profile/api-tokens. Once logged in, you should see a section called “API Tokens.” Click on the “View” button to see your Global API Key.

Find the Zone ID

The next step is to find the Zone ID of the domain you want to update. You can find the Zone ID by following the instructions provided in the Cloudflare documentation: https://developers.cloudflare.com/fundamentals/get-started/basic-tasks/find-account-and-zone-ids/.

Click on the domain you want to manage.
In the left-hand sidebar, click on “Overview.”
Scroll to the “API” section and click “Get your API key.”
Click on the “View” button next to the Global API Key.
Copy the key and keep it somewhere safe.

Get the DNS Record Identifiers

Once you have obtained the Zone ID, you can use it to get the identifiers for the DNS records associated with that domain. You can do this by making a GET request to the Cloudflare API, specifying the Zone ID, and providing your email address and API key. The response will contain information about all of the DNS records associated with the domain, including their identifiers.

Here is an example command that you can use to get the DNS record identifiers:

curl --request GET \
  --url https://api.cloudflare.com/client/v4/zones/<zone_id>/dns_records \
  --header 'Content-Type: application/json' \
  --header 'X-Auth-Email: <email_address>' \
  --header 'X-Auth-Key: <api_key>'

Replace <zone_id>, <email_address>, and <api_key> with your actual values.

Update the DNS Record

Finally, you can use the DNS record identifier to update the DNS record. The following is an example bash script that you can use to update a DNS record:

#!/bin/bash

ip=`curl https://bytefreaks.net/what-is-my-ip | grep '<h1 style="text-align: center;"' | cut -d '>' -f 2 | cut -d '<' -f 1`;

ip=`echo $ip | cut -d, -f1`;

comment=`date +%Y-%m-%d\ %H:%M`;

curl --request PUT \
  --url https://api.cloudflare.com/client/v4/zones/<zone_id>/dns_records/<dns_record_id> \
  --header 'Content-Type: application/json' \
  --header 'X-Auth-Email: <email_address>' \
  --header 'X-Auth-Key: <api_key>' \
  --data '{
  "content": "'$ip'",
  "name": "www.bytefreaks.net",
  "proxied": true,
  "type": "A",
  "comment": "'"$comment"'",
  "tags": [],
  "ttl": 3600
}'

Replace <zone_id>, <dns_record_id>, <email_address>, and <api_key> with your actual values. You should also update the "name" field to match the name of the DNS record you want to update.

This script is used to update a DNS record using the Cloudflare API. It retrieves the current public IP address of the device running the script and then updates the specified DNS record on Cloudflare with the new IP address.

Here is a breakdown of each command in the script:

ip=curl https://bytefreaks.net/what-is-my-ip | grep ‘<h1 style=”text-align: center;”‘ | cut -d ‘>’ -f 2 | cut -d ‘<‘ -f 1“: This command uses the curl command to retrieve the public IP address of the device running the script from the website https://bytefreaks.net/what-is-my-ip. The output of this command is then piped through grep to find the line that contains the IP address. The cut command is then used to extract the IP address from the line.
ip=echo $ip | cut -d, -f1“: This command removes any commas from the IP address, which may be present if the IP address is in a format that includes additional information.
comment=date +%Y-%m-%d\ %H:%M“: This command generates a comment for the DNS record update. The comment includes the current date and time in the format YYYY-MM-DD HH:MM.
curl --request PUT \: This command sends an HTTP PUT request to update the specified DNS record.
--url https://api.cloudflare.com/client/v4/zones/<zone_id>/dns_records/<dns_record_id> \: This specifies the URL for the Cloudflare API endpoint for updating a DNS record. The <zone_id> and <dns_record_id> placeholders should be replaced with the actual zone ID and DNS record ID, respectively.
--header 'Content-Type: application/json' \: This specifies that the content type of the request is JSON.
--header 'X-Auth-Email: <email_address>' \: This specifies the Cloudflare account email address associated with the API key. The <email_address> placeholder should be replaced with the actual email address.
--header 'X-Auth-Key: <api_key>' \: This specifies the Cloudflare API key for the account. The <api_key> placeholder should be replaced with the actual API key.
--data '{ ... }': This specifies the JSON data to be sent in the request body. This includes the new IP address in the content field, the domain name in the name field, the record type in the type field, the comment in the comment field, and other optional parameters like the ttl. Note that the domain name at.put.cy is hardcoded in the script, and should be replaced with the actual domain name to be updated.

Bytefreaks.net – a place for hacks