How to Create Your Own BTC Balance Checker with MySQL

Today, we are going to create a BTC positive balance checker database based on monthly data. This positive balance checker is designed for BTC address checkers who target forgotten or locked wallets. 

Creating such as database eaiser and also requires less energy than you probably think.

We should download the latest balance data. I will download data from here. You can visit that and find the latest one. In the data we will use, there are only addresses. It does not contain balances. If an address exists on data, it means it has positive value, it may be 0.00001 BTC or 10000 BTC.

We can use wget to download it.

wget -q --show-progress "https://github.com/Pymmdrza/Rich-Address-Wallet/releases/download/Bitcoin-Addr_Sep-2023/Just_All_P2PKH_Bitcoin_Addresses.txt.zip"

Unzip the archive:

unzip Just_All_Bitcoin_Addresses.txt.zip

Install MySQL Server on our machine.

sudo apt update && sudo apt install mysql-server -y

Connect to your MySQL Server:

sarp@ubuntu:/tmp$ sudo mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.34-0ubuntu0.22.04.1 (Ubuntu)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

To import our data, it has to be in the mysql-files folder. To find out the location or direction:

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)

mysql>

We must copy our file into /var/lib/mysql-files/

sudo cp Just_All_Bitcoin_Addresses.txt  /var/lib/mysql-files/


Create a new database:

mysql> CREATE DATABASE bitcoin_addresses;

Use the new database:

mysql> USE bitcoin_addresses;

Create a table to store the Bitcoin addresses:

mysql> CREATE TABLE addresses (id INT AUTO_INCREMENT PRIMARY KEY, bitcoin_address VARCHAR(35) NOT NULL);

This table will have one column, address.

Load the data from your TXT file into the table:
You can use the LOAD DATA INFILE statement.

In this SQL statement:

  • @bitcoin_address is a user-defined variable that temporarily holds the Bitcoin address from each line of your input file.
  • We set id to NULL because it's an auto-incrementing column, and MySQL/MariaDB will automatically assign values to it.
mysql> LOAD DATA INFILE '/var/lib/mysql-files/Just_All_P2PKH_Bitcoin_Addresses.txt'
INTO TABLE addresses
LINES TERMINATED BY '\n'
(@bitcoin_address)
SET id = NULL, bitcoin_address = @bitcoin_address;
Query OK, 22750440 rows affected (3 min 56.97 sec)                            
Records: 22750440  Deleted: 0  Skipped: 0  Warnings: 0                        

This statement loads data from your TXTfile into the bitcoin_addresses table. It may take a little time, 3-5 minutes.

Create an index on the address column for faster lookups:

mysql> CREATE INDEX idx_bitcoin_address ON addresses (bitcoin_address);
Query OK, 0 rows affected (2 min 15.46 sec)                                   
Records: 0  Duplicates: 0  Warnings: 0                                        

This will help improve query performance when searching for a specific Bitcoin address.

Seems everything is done. We can test it with this very first query.

SELECT * FROM addresses LIMIT 10 OFFSET 10;
+----------+------------------------------+
| id       | bitcoin_address              |
+----------+------------------------------+
| 16798357 | 11111111111111111111HeBAGj   |
| 18383423 | 11111111111111111111HV1eYjP  |
| 22650324 | 11111111111111111111jGyPM8   |
| 22404997 | 11111111111111111111o9FmEC   |
| 22662007 | 11111111111111111111QekFQw   |
| 18989534 | 11111111111111111111ufYVpS   |
| 22673168 | 11111111111111111111UpYBrS   |
| 16254473 | 111111111111111111121xzjPWX1 |
| 18394171 | 111111111111111111128gzo7iT  |
| 14345310 | 11111111111111111112AmVxQeF  |
+----------+------------------------------+
10 rows in set (0.00 sec)

Let's query this address if it has bitcoins.

mysql> SELECT * FROM addresses WHERE bitcoin_address = '1DonateWffyhwAjskoEwXt83pHZxhLTr8H';
+---------+------------------------------------+
| id      | bitcoin_address                    |
+---------+------------------------------------+
| 3286809 | 1DonateWffyhwAjskoEwXt83pHZxhLTr8H |
+---------+------------------------------------+
1 row in set (0.00 sec)

Seems like instantly found. Let's check how much does checking 50k wallets take.

 DELIMITER //
CREATE PROCEDURE CheckWallets()
BEGIN
    DECLARE counter INT;
    SET counter = 1;
    WHILE counter <= 10000 DO
        SELECT * FROM addresses
        WHERE bitcoin_address IN ('ffyhwAjskoEwXt83pHZr8H', '1DooEwXt83pHZxhLTr8H', '1DonateWffyhwpHZxhLTr8H', '1DonateWffyLTr8H', '1DonateWffyhwAjskoEwXt83pHZxhLTr8H');
        SET counter = counter + 1;
    END WHILE;
END //
DELIMITER ;
CALL CheckWallets();
...
...
...
+---------+------------------------------------+
| id      | bitcoin_address                    |
+---------+------------------------------------+
| 3286809 | 1DonateWffyhwAjskoEwXt83pHZxhLTr8H |
+---------+------------------------------------+
1 row in set (2.10 sec)

+---------+------------------------------------+
| id      | bitcoin_address                    |
+---------+------------------------------------+
| 3286809 | 1DonateWffyhwAjskoEwXt83pHZxhLTr8H |
+---------+------------------------------------+
1 row in set (2.10 sec)

Query OK, 0 rows affected (2.10 sec)

It took approximately 2.10 seconds for 50,000 addresses, meaning almost 24,000 addresses in a second. If we try with real values, we'll probably have better performance.